How to Reset the SA Password in SQL Server

This article will guide you through the process of resetting the forgotten System Administrator (SA) password in SQL Server using Microsoft's SQL Server program. You can achieve this by logging in with Windows Authentication, utilizing Command Prompt, or using Single-User Mode.

Understanding the Method: If Windows Authentication is enabled for your server, you can use it to log in without needing to enter a password. Once logged in, you can easily change the SQL Server password.

• If Windows Authentication is unavailable, you will need to use Single-User Mode or use Command Prompt.

Ensure that SSMS is installed. SSMS is a user interface that allows you to modify various SQL Server setup elements through a window, rather than using Command Prompt. If you haven't installed SSMS, follow these steps:

• Go to the SSMS download page in your browser.
• Click on the Download SQL Server Management Studio 17.6 link.
• Double-click the downloaded SSMS installer file.
• Follow the on-screen instructions to complete the SSMS installation.

Launch SSMS. Type sql server management studio in the Start menu, then click on Microsoft SQL Server Management Studio 17 at the top of the Start window.

Select the correct authentication method. Click on the "Authentication" dropdown and choose Windows Authentication from the menu.

Click the Connect button. This option, located at the bottom of the window, will take you to the server's dashboard if Windows Authentication is available for your account.

Expand the server folder. If the server folder in the top-left corner of the window doesn't show additional options below, click the ＋ icon to expand it.

Expand the "Security" folder. This option is located beneath the server name.

Expand the "Logins" folder. You will find this option within the set of choices under the "Security" folder.

Double-click on sa. This is the option under the "Logins" group. This action will open the properties window for the System Administrator (SA).

Enter a new password. Type the new password into both the "Password" and "Confirm password" fields near the top of the window.

Click the OK button at the bottom of the window. This action will update the password and close the properties window.

Understand the operation method. Even if you have locked your single account, you can still add users and grant administrator rights using the Command Prompt. Once this is done, you can use the new user’s information to log into the SQL Server page, and from there, change the SA password.

Ensure SSMS is installed. SSMS is the user interface that allows you to modify various settings of SQL Server within a window instead of using the Command Prompt. If SSMS is not yet installed, follow these steps:

• Go to the SSMS installation page in your browser.
• Click the link Download SQL Server Management Studio 17.6.
• Follow the on-screen instructions to install SSMS.

Stop SQL Instance. Type net stop MSSQLSERVER and press ↵ Enter. This action will stop the running SQL service.

Start SQL in single-user mode. Type net start MSSQLSERVER -m"SQLCMD" and press ↵ Enter.

• You won't see any indication that single-user mode is selected at this point, but you'll get the message "The SQL Server <MSSQLSERVER> service was started successfully".

Connect to SQL. Type sqlcmd and press ↵ Enter. This will open the SQL command line.

Create a new user and password. You will work with the commands already entered in the SQL command line:

• Type CREATE LOGIN name WITH PASSWORD=’password’ where "name" is the account name and "password" is the new password.
• Press ↵ Enter.
• Type GO and press ↵ Enter.

Add the user to the System Administrator role. Type SP_ADDSRVROLEMEMBER name,’SYSADMIN’ where "name" is the account name, then press ↵ Enter, then type GO and press ↵ Enter.

Exit the SQLCMD command line. Type exit and press ↵ Enter.

Restart SQL in normal mode. You can exit single-user mode by typing net stop MSSQLSERVER && net start MSSQLSERVER and pressing ↵ Enter.

• The message "The SQL Server service was started successfully" will appear again, signaling that you can close the Command Prompt.

Open SSMS. Type sql server management studio in the Start menu, and click on Microsoft SQL Server Management Studio 17 at the top of the window.

Select the correct authentication method. Click on the "Authentication" dropdown and choose SQL Server Authentication from the options provided.

Log in with the new user’s credentials. Click on the "Login" field and select the user you just created from the list.

Enter the password. Type the user’s password into the "Password" field located just below the window.

Click on Connect (Connect). This option is located at the bottom of the window. Once you enter the correct username and password, this action will open the server control panel.

Expand the server folder. If the server directory at the top left of the window does not show additional options beneath it, click the ＋ icon on the left to expand.

Open the "Security" folder. This option is located beneath the server name.

Expand the "Logins" folder. You will find this option in the group beneath the "Security" folder.

Double-click on sa. This option is found within the "Logins" group. Double-clicking this will open the System Administrator properties window.

Enter a new password. Input the new password in both the "Password" and "Confirm password" fields near the top of the window.

Click on the OK button at the bottom of the window. This will change the password and close the properties window.

Search for Command Prompt. Type command prompt, then wait for Command Prompt to appear at the top of the Start menu.

Click on Run as administrator (Run with administrator privileges). This option appears in the displayed menu.

Click on Yes when prompted. This will confirm the decision to open Command Prompt in administrator mode. The Command Prompt window will then appear immediately.

Enter the first command. Type osql -L and press ↵ Enter.

Enter the second command with your server's name. Type OSQL -S server -E replacing "server" with your server's name, and press ↵ Enter.

Create a new password. Type EXEC sp_password NULL, ‘password’, ’sa' where "password" is the new password you wish to set, then press ↵ Enter.

• For example, to set the password as "rutabaga123", you would type EXEC sp_password NULL, 'rutabaga123', 'sa' in the Command Prompt.

Execute the command. Type GO and press ↵ Enter. Then, type exit and press ↵ Enter to exit OSQL.

Attempt to log in to SQL Server. You will use your administrator credentials and the new password. If you can successfully log in, it means the password has been updated successfully.

• If these methods do not work for your case, you can try using some paid SQL Server password recovery programs available online.

• You cannot reset the SA password remotely.