Well it’s not really a hack now is it, but it sounds way cool!

What happens in SQL 2008 R2 is that sysadmin rights are not automatically given to the local administrator of the machine.  This has caused some issues for those who require sysadmin access and didnt setup the SQL instance.  So what do you do if you need sysadmin rights, and you are a local admin?

• Open command prompt as Administrator
• If you have default instance run following command on command prompt to stop SQL service: “net stop mssqlserver”
• Now go to the directory where SQL server is installed. In my case the directory is “C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn”. So need to run CD C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn.
• Now run the following command to start SQL server in single user mode. As we have specified “SQLCMD”, now only SQLCMD connection can be made.

sqlservr -m”SQLCMD”

• Now open another command prompt window and write command SQLCMD
• Run following two commands, remember to change DOMAINUSERNAME for the user you are currently logged in as

create login [<<DOMAINUSERNAME>>] from windows; 

EXEC sys.sp_addsrvrolemember @loginame = N’<<DOMAINUSERNAME>>’, @rolename = N’sysadmin’; 

GO;

• Go back to first command window and press Ctrl+C to stop the SQL server and then type ‘Y’ for confirmation. This will stop the SQL server.
• Start SQL server again and no startup parameters need to specified this time.
• Login as the user that you are logged in with using SQL Server Management Studio

Credit where credit is due.

How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password