How to restore administrator access to a SQL server
I recently obtained a development machine that had SQL Server 2008 installed. Unfortunately, I got this error:
CREATE DATABASE permission denied in database 'master'
Despite being an administrator on the box, AND having launched Management Studio in Administrator mode (on Windows 7). Attempts to grant myself permissions, or to make any changes to SQL server resulted in:
User does not have permission to perform this action( Microsoft SQL Server, Error:15247)
This all happened because I was not a SQL Server administrator despite being an administrator on the box itself. This is a new situation in SQL Server 2008. In SQL Server 2005, the local Administrators group was part of the SQL Server administrators. It turns out that only the person who installed SQL Server is a SQL Server administrator. Since that person was not available, I was faced with either uninstalling and reinstalling, or getting into an argument with SQL Server.
Being stubborn, I chose to argue. I knew as an Administrator I could do anything I wanted, it was just a matter of finding the right registry keys/files/whatever to add myself to the group. The trick is to put SQL serve rinto single-user maintenance mode so that it ignores authentication.
NOTE: In all of the examples below, you may have to change parameters or command-lines based on your server name and instance name.
Force SQL server to support mixed-mode authentication
- Run REGEDIT
- Go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
NOTE: This key wiil vary slightly based on the installed version and instance name.
- Set "LoginMode" to 2.
- Restart SQL Server.
Force SQL server to let you in temporarily
- Go to services.
- Stop SQL Server.
- Grab the SQL server command-line (right click the service - properties). Mine
"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
- Open an administrative command prompt.
- Run the command-line from step 3, but add
-m -cfor single-user maintenance mode command-line.
- Open another administrative command prompt.
"sqlcmd -S localhost\SQLEXPRESS"from that same directory (replace with your server and instance name)
- Now you can do all the stuff everyone told you to do that didn"t work. For
example, to create a hero user with administrative access:
CREATE LOGIN hero WITH PASSWORD="123", DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginname = "hero", @rolename = "sysadmin"
QUITand close the command-prompt
- Go to the SQL Server command-line window and hit ctrl+C. It will prompt "Do you wish to shutdown SQL Server (Y/N)?" and enter Y.
- Close the command-prompt
Finally, login using your hero
- Restart the SQL Server service
- Login using SQL Server authentication as the user "hero" with password "123"
- *BAM* now you are in. Now give yourself sysadmin access, etc, etc.