TN WW257 How to reset lost SA password in MS SQL Server Express
Description
This article from InSource shows how to reset the sa password in MS SQL Server/Express.
- Author: Rufus Handsome
- Published: 07/25/2019
- Applies to: MS SQL Server 2014 and 2014 Express
Details
If you don't have sysadmin rights to your SQL Server instance, first you will need to change the login method by changing a registry setting.
1.) Edit the registry settings:
- Open the registry editor
- Navigate to "
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer" OR equivalent.
- Note - One thing to note, you may have to search for the "LoginMode" entry using the registry search functions.
- Change the value of LoginMode from 1 to 2
- Close the registry editor
2.) Edit Windows Services
- Open the Services applet in the Control Panel
- Find the “SQL Server (SQLEXPRESS or equivalent instance)” entry and open its properties
- Stop the service
- Enter “-m” into the “Start parameters” field
- Start the service
- Open a Command Prompt
- Enter the command: "osql -S PC_Name -E" or "osql -S PC_NAME\SQLEXPRESS -E"
- (Note - Substitute "PC_Name" with your computer name, )
3.) Enter the following Commands at the prompt
- 1> alter login sa enable
- 2> go
- 1> sp_password NULL,'new_password','sa'
- 2> go
- 1> quit
4.) Stop the “SQL Server (SQLEXPRESS)” service
5.) Remove the “-m” from the Start parameters field
6.) Start the service
7.) Try to login into SQL Management Studio with the sa account and new password.