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 thirteen years of experience architecting scalable, distributed, high traffic web applications. I currently live in Woodinville, WA with my wife, two daughters, three dogs and cat. I work for Microsoft as a Sr. Software Engineer working in Cloud Developer Services. I'm also project founder and owner of http://www.requestreduce.org and a committer to http://chocolatey.org.

Month List