What to do when you cant access Sql Server as Admin

27. November 2012 03:05 by Matt Wrock in   //  Tags:   //   Comments (0)
DotNetShoutout
DotnetKicks

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.

Add comment

biuquote
  • Comment
  • Preview
Loading

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