What to do when you cant access Sql Server as Admin

27. November 2012 03:05 by Matt Wrock in   //  Tags:   //   Comments

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.

blog comments powered by Disqus

About Me

Hey thats me!

I'm Matt Wrock with over fifteen years of experience architecting scalable, distributed, high traffic web applications as well as environment and deployment automation. I currently live in Woodinville, WA with my wife, two daughters, four dogs and two cats. Until just recently I worked for Microsoft as a Sr. Software Engineer and now work for CenturyLink Cloud focusing on data center automation. I'm also project founder of http://Boxstarter.org and a committer to http://chocolatey.org.

Month List