I work on a lot of different customer’s computers. And once in a while I find a SQL Server that nobody knows the SA password and it is not in our database of passwords. And of course I don’t want to go back to one of our customers and tell them that we don’t have access. Or once in my case the DBA that was suppose to tell me about all of the SQL Servers “forgot” to tell me about a few servers and I found them after she was fired. Or once I found some old test servers that people forgot about and I needed to get access to them. And of course you don’t want any downtime.
What you need to do is simple, you need a program from SysInternals called PsExec. You can download it here – https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx – make sure you get it from Microsoft, they purchased Sysinternals a long time ago. Place a copy of PsExec.exe in a path directory, your System32 directory would work, or if you want to run this from the command line put a copy in the directory you are working from. You need to know the path to SSMS.EXE and the fastest way for me to tell you to find it is to look at the properties of SSMS icon, just highlight and copy the target path.
Your path will very depending on version and where you did your install. In my case I put a copy in c:\windows\System32 and I just did a start – run
PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”.
If this is the first time you have run the PsExec program it will ask you to accept the license. Say Agree.
You will now be prompted to log into your SQL Server instance using windows authentication. Do it and you will be logged in as the NT AUTHORITY\SYSTEM account. I then add an account or reset the SA password and then log in under that account. Hopefully you are not using SA to run any applications so be careful if you reset that password. Adding an account is the safest thing to do.
You now can log into your SQL Server without have to restart the services or having to reinstall SQL Server.
Remember you need to be a local admin in order to log in as the NT AUTHORITY\SYSTEM. Download PsExec and keep it in your tool belt. You never know when you will need it.