Inherited a SQL Server with no way to login?
Ya that’s happened to me. I administrative access to the server, but couldn’t log into SQL. What’s even better, no one else knew the login and it wasn’t documented anywhere either. What’s a DBA to do? Google to the rescue. I came across an article on MSSQLTips.com that explained how to regain access.
In the article linked below Aaron Bertrand has given step by step instructions on regaining access to SQL Server.
My summary of the process
In short, download an app called PsExec and do the following:
“The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, “Run as Administrator”). Run the following command, adjusting for your actual path to Management Studio, which may be different.PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”
This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).”
Once connected, you can go to properties to add the needed accounts under Security > Logins in SSMS.
By no means am I trying to either trade off of Aaron Bertrand’s work or pass it off as my own. I am also not using his post as a means to repost for hits, I simply posted a quick summary for my own needs, should I ever need this again. I’m positive both the people at MSSQLTips, as well as Mr. Bertrand, have forgotten more about SQL than I ever hope to learn.