This article shows you how to recover the forgotten password of the System Administrator user (known simply as SA) of an instance of Microsoft SQL Server. You can solve this problem in several ways: using the "Windows Authentication" authentication method, using the "Command Prompt", or using the "single user" mode.
Steps
Method 1 of 3: Use Windows Authentication
Step 1. Understand how this method works
If you have enabled the use of Windows authentication, you will be able to access the server without the need to enter any password. After logging in, you will be able to change the password of the SQL Server SA account very easily.
If the "Windows Authentication" authentication mode is not enabled, you can change the password using the "single user" mode or the "Command Prompt"
Step 2. Make sure you have installed the "SQL Server Management Studio" program, known simply as SSMS
It is a graphical user interface that allows you to manage various aspects of the configuration of a Microsoft SQL Server rather than having to use the "Command Prompt". If you haven't installed SQL Server Management Studio on your server yet, follow these instructions:
- Access this web page using your computer browser;
- Click the link Download SQL Server Management Studio 17.9.1;
- At the end of the download, double-click on the program installation file;
- Follow the instructions to be able to install SSMS on your computer.
Step 3. Start the SQL Server Management Studio program
Type sql server management studio keywords in the "Start" menu, then click the icon Microsoft SQL Server Management Studio 17 appeared at the top of the results list.
Step 4. Select the correct authentication method
Click the "Authentication" drop-down menu, then click the entry Windows authentication.
Step 5. Click the Connect button
It is located in the lower right part of the dialog box. If the "Windows Authentication" mode allows you to log into SQL Server with your user account, the server administration dashboard will be displayed.
Step 6. Expand the SQL Server instance folder
If a series of folders is not visible in the left pane of the SQL Server Management Studio GUI, click on the small icon ＋ to the left of the server name to view all related information
Step 7. Go to the "Security" folder
It is listed under the Microsoft SQL Server instance name.
Step 8. Open the "Login Accounts" folder
It is one of the items contained in the "Security" folder.
Step 9. Double-click the sa account
It is displayed at the bottom of the list of items that appeared under the "Login Accounts" folder. This will bring up the Microsoft SQL Server System Administrator account properties window.
Step 10. Set a new password
Type the new login password into the "Password" and "Confirm password" text fields at the top of the dialog box that appears.
Step 11. Click the OK button
It is located at the bottom of the window. This will cause the new password to be stored and applied and the SA account properties window will be closed.
Method 2 of 3: Use Single User Mode
Step 1. Understand how this method works
Even if you have locked out access to the only existing account using the "Command Prompt", you will still be able to create a new user and assign him the access rights of the SQL Server SA account. After performing this step you will be able to use the new user to connect to the SQL Server instance and change the password of the SA user.
Step 2. Make sure you have installed the "SQL Server Management Studio" program known simply as SSMS
It is a graphical user interface that allows you to manage various aspects of the configuration of a Microsoft SQL Server rather than having to use the "Command Prompt". If you haven't installed SQL Server Management Studio on your server yet, follow these instructions:
- Access this web page using your computer browser;
- Click the link Download SQL Server Management Studio 17.9.1;
- At the end of the download, double click on the program installation file;
- Follow the instructions to be able to install SSMS on your computer.
Step 3. Launch the "Command Prompt" in system administrator mode
Access the menu Start clicking the icon
then follow these instructions:
- Type in the keywords command prompt;
-
Click the Command Prompt
with the right button of the muse;
- Click the entry Run as administrator;
- Click on the button Yup when required.
Step 4. Stop running the SQL Server instance
Type the command net stop MSSQLSERVER in the "Command Prompt" window and press the Enter button. This will cause the SQL Server service to be stopped.
Step 5. Restart the SQL Server in "single user" mode
Type the command net start MSSQLSERVER -m "SQLCMD" and press the Enter key;
You will not see any notification that the SQL Server was started in "single user" mode, but the sentence "The SQL Server service has started successfully" should appear
Step 6. Connect to the SQL Server
Type the sqlcmd command and press the Enter key. The SQL server command line will be displayed.
Step 7. Create a new user and a new password
Use the SQL command line that appeared and the following statements:
- Type the command CREATE LOGIN [name] WITH PASSWORD = '[pwd]', where the parameter "[name]" represents the name of the new account and the parameter "[pwd]" the relative access password;
- Press the Enter key;
- Type the GO command and press the Enter key.
Step 8. Add the new user to the SQL Server "System Administrator" group
Type the command SP_ADDSRVROLEMEMBER [name], 'SYSADMIN', where the parameter "[name]" represents the name of the new account just created, press the Enter key, type the GO command and press the Enter key again.
Step 9. Close the SQL Server Command Console
Type the command exit and press the Enter key.
Step 10. Restart the SQL Server instance service in normal mode
Run the command net stop MSSQLSERVER && net start MSSQLSERVER and press the Enter key.
The "SQL Server service has started successfully" message should appear again. At this point you can close the "Command Prompt" window
Step 11. Start the SQL Server Management Studio program
Type sql server management studio keywords in the "Start" menu, then click the icon Microsoft SQL Server Management Studio 17 appeared at the top of the results list.
Step 12. Select the correct authentication method
Click the "Authentication" drop-down menu, then click the entry SQL Server authentication.
Step 13. Log in with the credentials of the new user you just created
Click the "Login" drop-down menu and click the user name to use.
Step 14. Enter your password
Type in the "Password" text field at the bottom of the window.
Step 15. Click the Connect button
It is located in the lower right part of the dialog box. If you entered the correct username and password, the server administration dashboard will be displayed.
Step 16. Expand the SQL Server instance folder
If a series of folders is not visible in the left pane of the SQL Server Management Studio GUI, click on the small icon ＋ to the left of the server name to view all related information
Step 17. Go to the "Security" folder
It is listed under the Microsoft SQL Server instance name.
Step 18. Open the "Logins" folder
It is one of the items contained in the "Security" folder.
Step 19. Double-click the sa account
It is displayed at the bottom of the list of items that appeared under the "Login Accounts" folder. This will bring up the Microsoft SQL Server System Administrator account properties window.
Step 20. Set a new password
Type the new login password into the "Password" and "Confirm password" text fields at the top of the dialog box that appears.
Step 21. Click the OK button
It is located at the bottom of the window. This will cause the new password to be stored and applied and the SA account properties window will be closed.
Method 3 of 3: Use the Command Prompt
Step 1. Access the "Start" menu by clicking the icon
It features the Windows logo and is located in the lower left corner of the desktop. Alternatively, press the ⊞ Win key on your keyboard.
Step 2. Search for "Command Prompt"
Type the keywords command prompt into the "Start" menu, then wait for the "Command Prompt" icon to appear.
Step 3. Click the Command Prompt icon
with the right mouse button.
A context menu will be displayed.
Step 4. Click Run as administrator
It is one of the options listed in the menu that appeared.
Step 5. Click the Yes button when prompted
This will confirm your action and the "Command Prompt" window will be displayed in system administrator mode.
Step 6. Enter the first command to execute
Type the command osql -L and press the Enter key.
Step 7. Enter the next command by adding the SQL Server name
Type the command OSQL -S [server] -E, where the "[server]" parameter should be replaced with the instance name of the SQL Server server, then press the Enter key.
Step 8. Create a new security password for the SA account
Type the command EXEC sp_password NULL, '[pwd]', 'sa', where the parameter "[pwd]" should be replaced with the password you want to use, then press the Enter key.
For example, if you want to set the new password "rutabaga123", you will need to execute this command EXEC sp_password NULL, 'rutabaga123', 'sa' within the "Command Prompt" window
Step 9. Run the command
Type the GO command and press the Enter key. At this point type the command exit and press the Enter key to close the OSQL command console.
Step 10. Try to log in to the SQL Server using your SA account credentials
If you are able to login to the server, it means that the security password has been changed successfully.
