How to restore administrator access to a SQL server

2011-02-19

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\MSSQLServerNOTE: This key wiil vary slightly based on the installed version and instance name.

Run the command-line from step 3, but add -m -c for single-user maintenance
mode command-line.

Open another administrative command prompt.

Run "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=OFFEXEC sys.sp_addsrvrolemember @loginname = "hero", @rolename = "sysadmin"GO

QUIT and 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.