Reset MSSQL Login

You have Admin rights to the machine, but you set up Microsoft SQL Server as SQL only login with SA, but you’ve forgotten your password. Here’s the dead easy way to reset this, including adding the Admin account and setting it for dual authentication.

Note: due to lots of machine names, etc, I’m going to use any pictures to reduce any security issues.

I did this for MSSQL 2008 R2, but it should work just about everywhere.

On the desktop

1. Logon as Administrator (local or network)
2. Stop the SQL Server
3. Open Command as Admin
4. You will need the path to that SQL instance. I open Explorer, find it, then copy the path. It should be something like this:

   C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

5. In CMD, CD to that directory, then type (don’t forget the period – case doesn’t matter):

    .\SQLServr.exe -m

6. You need to keep the CMD window open as SQL is now running in that single user session. If you close it accidentally you have to restart this process.
7. Start SQL Management Studio.
   1. Connect to your SQL instance
   2. Go to security > logins > sa > Properties
   3. Click Status and make sure you’re not locked out. If you are, you need to uncheck “Login is locked out”, click General and re-enter or change the sa password. Hit Ok and close out of that.
   4. If you’re switching to/from multi auth mode
      1. In SMS, right click on the server instance > properties
      2. Click on security
      3. Select your authentication mode
      4. Don’t forget to add your Windows admin user under Security!
   5. Close SMS
8. Switch to the open CMD and press CTRL-C. For some reason I have to do this a couple times. Answer Yes to stop the instance.
9. Start SQL and then connect using SMS
10. Done!