Content

• Steps
• Method 1 of 3: Using Windows Authentication
• Method 2 of 3: Using Single-User Mode
• Method 3 of 3: Using the Command Line
• Tips
• Warnings

This article will show you how to reset the lost System Administrator (SA) password in Microsoft SQL Server. To do this, you will need to log in through Windows authentication, use the command line, or single user mode.

Steps

Method 1 of 3: Using Windows Authentication

1. 1 Learn all the nuances of this method. If Windows Authentication is enabled on the server, you can use Windows Authentication to log on to the server without having to enter a password. Once logged in, change the SQL server password.
   • If Windows Authentication is disabled, use single user mode or Command Prompt to reset your password.
2. 2 Make sure SSMS is installed on the computer. SSMS is a user interface that allows you to change various SQL Server parameter settings in a window rather than on the command line. If you don't have SSMS, do the following:
   • open the SSMS download page in your browser;
   • click the link Download SQL Server Management Studio 17.6;
   • double click on the downloaded SSMS installation file;
   • follow the instructions on the screen to install SSMS.
3. 3 Start SSMS. Enter sql server management studio from the start menu and then select Microsoft SQL Server Management Studio 17 at the top of the menu.
4. 4 Select Authentication. From the "Authentication" dropdown, select Windows Authentication.
5. 5 Press Connect at the bottom of the window. If Windows Authentication is enabled on your account, you will be taken to the Server Control Panel.
6. 6 Expand the server folder. If there are no additional folders under the server directory in the upper left of the window, click the + sign to display them.
7. 7Expand the Security folder.
8. 8 Expand the Logins folder. This is one of the folders in the Security directory.
9. 9Double click on item sa in the Logins folder to open the System Administrator Properties window.
10. 10 Enter a new password. Enter your new password in the Password and Confirm Password text boxes at the top of the window.
11. 11Click on OK at the bottom of the window to change the password and close the properties window.

Method 2 of 3: Using Single-User Mode

1. 1 Learn all the nuances of this method. Even if you lock your only account, it doesn't stop you from adding a new user and giving him administrator rights via the command line. Use the credentials of the new user to get to the SQL Server page and reset the password.
2. 2 Make sure SSMS is installed on the computer. SSMS is a user interface that allows you to change various SQL Server parameter settings in a window rather than on the command line. If you don't have SSMS, do the following:
   • open the SSMS download page in a browser;
   • click the link Download SQL Server Management Studio 17.6;
   • double click on the downloaded SSMS installation file;
   • follow the instructions on the screen to install SSMS.
3. 3 Open a command prompt with administrator rights. Open the menu Startand then follow these steps:
   • enter command line;
   • right click on the result Command line;
   • Press Run as administrator;
   • when prompted, select Yes.
4. 4 Stop the SQL Server services. Enter net stop MSSQLSERVER and press ↵ Enterto stop running SQL services.
5. 5 Restart SQL in single user mode. Enter net start MSSQLSERVER -m "SQLCMD" and press ↵ Enter.
   • At this point, you will not see any indication that the program is running in single user mode. This will be evidenced only by the phrase: "The SQL Server MSSQLSERVER> service was started successfully".
6. 6 Connect to SQL. Enter sqlcmd and press ↵ Enterto run the SQL command line.
7. 7 Create a new user and password by entering the following commands at the SQL command line:
   • enter CREATE LOGIN name WITH PASSWORD = 'password'where "name" is the account name and "password" is the new password;
   • click ↵ Enter;
   • enter GO and press ↵ Enter.
8. 8 Assign the system administrator role to the user. Enter SP_ADDSRVROLEMEMBER name, 'SYSADMIN'where "name" is the name of the account, click ↵ Enterand then enter GO and press ↵ Enter.
9. 9 Exit the SQLCMD command line. Enter exit and press ↵ Enter.
10. 10 Restart SQL normally. To exit single user mode, enter net stop MSSQLSERVER && net start MSSQLSERVER and press ↵ Enter.
    • You will see the line again: "The SQL Server MSSQLSERVER> service was started successfully". From now on, you no longer need the command line.
11. 11 Start SSMS. Enter sql server management studio from the start menu and then select Microsoft SQL Server Management Studio 17 at the top of the menu.
12. 12 Select Authentication. From the "Authentication" dropdown, select SQL Server Authentication.
13. 13 Log in with the credentials of the new user. Click on the "Username" drop-down list and select the name of the new user.
14. 14 Enter password. Enter the user's password in the Password field at the bottom of the window.
15. 15 Press Connect at the bottom of the window. If you entered the username and password correctly, you will be taken to the server control panel.
16. 16 Expand the server folder. If there are no additional folders under the server directory in the upper left of the window, click the + sign to display them.
17. 17Expand the Security folder.
18. 18 Expand the Logins folder. This is one of the folders in the Security directory.
19. 19Double click on item sa in the Logins folder to open the System Administrator Properties window.
20. 20 Enter a new password. Enter your new password in the Password and Confirm Password text boxes at the top of the window.
21. 21Click on OK at the bottom of the window to change the password and close the properties window.

Method 3 of 3: Using the Command Line

1. 1 Open the start menu . Click on the Windows logo in the lower left corner of the screen to open the Start menu.
2. 2 Open a command prompt. Enter command line, and then select it at the top of the Start menu.
3. 3Right click on Command line to display the dropdown menu.
4. 4Select an option Run as administrator.
5. 5 Click on Yesto confirm to run the command prompt as an administrator. A command prompt window will open.
6. 6 Enter the first command. Enter osql -L and press ↵ Enter.
7. 7 Enter the second command with the server name. Enter OSQL -S server -Ewhere "server" replace the name of your server, then click ↵ Enter.
8. 8 Create a new password. Enter sp_password NULL, ‘password’, ’sawhere "password" replace the password you want to use, then press ↵ Enter.
   • For example, to set the password "rutabaga123", enter sp_password NULL ’rutabaga123’, ’sa.
9. 9 Attempt to log into SQL Server using administrator credentials and a new password. If you are able to log into SQL Server, then the password was successfully changed.

Tips

• If none of these methods work, you can find several paid SQL Server password recovery software on the Internet.

Warnings

• The administrator password cannot be reset remotely.