How to reset lost SA password in SQL Express

Overview

If you ever find yourself in a situation where you need to reset the sa password on a SQL Express instance then you are not alone. I recently inherited a PC from a previous developer that already had SQL Express installed. This normally wouldn’t be a bad thing, except in this case it was not setup in mixed mode and my logged in account had no sysadmin rights on the instance.

In this article I hope to enlighten you as to how to reset the sa password in SQL Express without having any sort of administrator rights to the SQL instance.

You will need to be somewhat comfortable with editing the registry, starting and stopping windows services and working with a command prompt.

Steps to SQL Express sysadmin access

In order to gain sysadmin access to the SQL instance there were a few things that I needed to accomplish. Here is the basic goals I needed to achieve:

• Change the login method from Windows Authentication to Mixed mode
• Enable the sa user account
• Reset the password to the sa user account

Doesn’t seem like that daunting of a list does it? I didn’t think so either.

Change the login method

In order to change the login method I had to change a registry setting. Why, you ask? Because, I say, we don’t have sysadmin rights so we can’t just change the database properties using SSMS.

• Open the registry editor
• Navigate to…

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer

• Change the value of LoginMode from 1 to 2
• Close the registry editor

One thing to note is I had a few different options under the “Microsoft SQL Server” branch so you may need to take an educated guess if yours is different.

Enable the sa user account and reset its password

I lumped these two into one section because the commands for them are entered at the same place.

1. Open the Services applet in the Control Panel
2. Find the “SQL Server (SQLEXPRESS)” entry and open its properties
3. Stop the service
4. Enter “-m” into the “Start parameters” field
5. Start the service
6. Open a Command Prompt
7. Enter the command:

osql -S PC_NAME\SQLEXPRESS -E

Be sure to change PC_NAME to whatever your PC name is or you will get a big long message saying you messed up.

8. At the next prompts enter the following commands:

1> alter login sa enable
2> go
1> sp_password NULL,'new_password','sa'
2> go
1> quit

9. Stop the “SQL Server (SQLEXPRESS)” service
10. Remove the “-m” from the Start parameters field
11. Start the service

At this point you should be able to login to SSMS using the sa user account and the new password you gave it.