What to do when you cant access Sql Server as Admin / by Matt Wrock

This happens to me on a particular VM setup framework we use at work about every couple months and I always have to spend several minutes looking it up. Well no longer I say. I shall henceforth document these steps so I will never have to wander the internets again for this answer.

Stop-Service mssqlserver -Force& 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' -m

sqlcmd -S "(local)"
CREATE LOGIN [REDMOND\mwrock] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
sp_addsrvrolemember 'REDMOND\mwrock', 'sysadmin'
GO
EXIT

Start-Service mssqlserve
 

This Stops sql server, then starts it in single user mode. You will then need to open a second shell to run the login creation script. When the user is created in the correct role, go back to the shell running the sql instance and exit via ctrl-C. Finally start sql server normally and you are good to go.