This article was co-authored by wikiHow staff writer, Jack Lloyd. Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher.
The wikiHow Tech Team also followed the article's instructions and verified that they work.
This article has been viewed 1,073,171 times.
Learn more...
This wikiHow teaches you how to reset a forgotten System Administrator (SA) password in Microsoft's SQL Server. You can do this by logging in with Windows Authentication, using Command Prompt, or using Single-User Mode.
Steps
Using Windows Authentication
-
1Understand how this method works. If Windows Authentication is enabled for your server, you can use it to log into your server without having to enter a password. After you're logged in, you can easily change the SQL server's password.[1]
- If Windows Authentication isn't enabled, you'll need to either use Single-User Mode or use Command Prompt to reset your password.
-
2Make sure that SSMS is installed. SSMS is a user interface which allows you to change different aspects of your SQL Server settings in a window rather than in Command Prompt. If you don't have SSMS installed, do the following:
- Go to the SSMS installation page in a browser.
- Click the Download SQL Server Management Studio 17.6 link.
- Double-click the downloaded SSMS setup file.
- Follow the on-screen prompts to install SSMS.
Advertisement -
3Open SSMS. Type sql server management studio into Start, then click Microsoft SQL Server Management Studio 17 at the top of the Start window.
-
4Select the correct authentication. Click the "Authentication" drop-down box, then click Windows Authentication in the menu.
-
5Click Connect. It's at the bottom of the window. If Windows Authentication is allowed for your account, doing so will log you into your server's dashboard.
-
6Expand your server's folder. If your server's folder in the upper-left side of the window doesn't have several options below it, click the + icon to the left of it to expand it.
-
7Expand the "Security" folder. It's below the server's name.
-
8Expand the "Logins" folder. You'll find this in the group of options below the "Security" folder.
-
9Double-click sa. It's in the "Logins" group of options. Doing so opens your System Administrator properties window.
-
10Enter a new password. Type your new password into both the "Password" and the "Confirm password" text fields near the top of the window.
-
11Click OK. It's at the bottom of the window. Doing so will change your password and close the properties window.
Using Single-User Mode
-
1Understand how this method works. Even if you've locked out your only account, you can add a user and give them administrator permissions by using the Command Prompt. After doing this, you can use the new user's credentials to log into your SQL Server page, from which point you can change the SA password.[2]
-
2Make sure that SSMS is installed. SSMS is a user interface which allows you to change different aspects of your SQL Server settings in a window rather than in Command Prompt. If you don't have SSMS installed, do the following:
- Go to the SSMS installation page in a browser.
- Click the Download SQL Server Management Studio 17.6 link.
- Double-click the downloaded SSMS setup file.
- Follow the on-screen prompts to install SSMS.
-
3
-
4Stop SQL Instance from running. Type in net stop MSSQLSERVER and press ↵ Enter. This will stop the currently running SQL services.
-
5Restart SQL in Single-User Mode. Type in net start MSSQLSERVER -m"SQLCMD" and press ↵ Enter.
- You won't see any indication that you're running in Single-User Mode at this point, but you should see the phrase "The SQL Server <MSSQLSERVER> service was started successfully" appear.
-
6Connect to SQL. Type in sqlcmd and press ↵ Enter. Doing so opens the SQL command line.
-
7Create a new user and password. You'll do this with typed commands in the SQL command line:
- Type in CREATE LOGIN name WITH PASSWORD=’password’ where "name" is the account name and "password" is the new password.
- Press ↵ Enter.
- Type in GO and press ↵ Enter.
-
8Add the user to the System Administrator role. Type in SP_ADDSRVROLEMEMBER name,’SYSADMIN’ where "name" is the account name, press ↵ Enter, and then type in GO and press ↵ Enter.
-
9Exit the SQLCMD command line. Type in exit and press ↵ Enter.
-
10Restart SQL in regular mode. You can cancel Single-User Mode by typing in net stop MSSQLSERVER && net start MSSQLSERVER and pressing ↵ Enter.
- You should see the "The SQL Server <MSSQLSERVER> service was started successfully" phrase appear again; at this point, you can close Command Prompt.
-
11Open SSMS. Type sql server management studio into Start, then click Microsoft SQL Server Management Studio 17 at the top of the Start window.
-
12Select the correct authentication. Click the "Authentication" drop-down box, then click SQL Server Authentication in the menu.
-
13Log in with the new user's credentials. Click the "Login" drop-down box, then click the name of the user you just created.
-
14Enter the password. Type the user's password into the "Password" text box near the bottom of the window.
-
15Click Connect. It's at the bottom of the window. As long as you adequately entered your username and password, this will open your server's dashboard.
-
16Expand your server's folder. If your server's folder in the upper-left side of the window doesn't have several options below it, click the + icon to the left of it to expand it.
-
17Expand the "Security" folder. It's below the server's name.
-
18Expand the "Logins" folder. You'll find this in the group of options below the "Security" folder.
-
19Double-click sa. It's in the "Logins" group of options. Doing so opens your System Administrator properties window.
-
20Enter a new password. Type your new password into both the "Password" and the "Confirm password" text fields near the top of the window.
-
21Click OK. It's at the bottom of the window. Doing so will change your password and close the properties window.
Using Command Prompt
-
1
-
2Search for Command Prompt. Type in command prompt, then wait for Command Prompt to appear at the top of the Start menu.
-
3
-
4Click Run as administrator. It's in the drop-down menu.
-
5Click Yes when prompted. This will confirm your decision to open Command Prompt in administrator mode. The Command Prompt window should open.
-
6Enter the first command. Type in osql -L and press ↵ Enter.[3]
-
7Enter the second command with your server's name. Type in OSQL -S server -E where "server" is replaced by your server's name, then press ↵ Enter.
-
8Create a new password. Type in EXEC sp_password NULL, ‘password’, ’sa' where "password" is replaced by the password that you want to use, then press ↵ Enter.
- For example, to set your password as "rutabaga123", you would type EXEC sp_password NULL, 'rutabaga123', 'sa' into Command Prompt.
-
9Execute the command. Type in GO, then press ↵ Enter. Type exit, then press ↵ Enter to exit OSQL.
-
10Attempt to log into SQL Server. Do so by using your administrator credentials and your new password. If you're able to log into SQL Server, your password was successfully changed.
Warnings
- You cannot reset your SA password remotely.⧼thumbs_response⧽
References
About This Article
1. Open SSMS.
2. Connect to the server.
3. Expand the server's folder.
4. Expand the Security folder.
5. Expand the Logins folder.
6. Double-click sa.
7. Enter and confirm a new password.
8. Click OK.