How do I grant myself admin access to a local SQL Server instance?

Question

I installed SQL Server 2008 R2 to my local machine. But, I can't create a new database because of rights (or lack of).

"CREATE DATABASE PERMISSION DENIED"

So, I tried to assign the admin privileges to my current login

"User does not have permission to perform this action."

I also tried to create a new login that would have admin privileges but with no luck. How do I grant myself admin rights so that I can create a database? I can re-install, but I prefer not to.

Answer 1

Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:

net stop mssqlserver 

Now go to the directory where SQL server is installed. The directory can for instance be one of these:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn 

Figure out your MSSQL directory and CD into it as such:

CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn 

Now run the following command to start SQL Server in single user mode. As SQLCMD is being specified, only one SQLCMD connection can be made (from another command prompt window).

sqlservr -m"SQLCMD" 

Now, open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:

sqlcmd 

And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode:

create login [<<DOMAIN\USERNAME>>] from windows;  -- For older versions of SQL Server: EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';  -- For newer versions of SQL Server: ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>];  GO 

Source.

UPDATED Do not forget a semicolon after ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>]; and do not add extra semicolon after GO or the command never executes.

Answer 2

Yes - it appears you forgot to add yourself to the sysadmin role when installing SQL Server. If you are a local administrator on your machine, this blog post can help you use SQLCMD to get your account into the SQL Server sysadmin group without having to reinstall. It's a bit of a security hole in SQL Server, if you ask me, but it'll help you out in this case.