9
Yehoram Dan

Yehoram Dan

Professional SQL server DBA with over 20 years of experience with enterprise organizations. Yehoram is responsible for consulting and providing advanced solutions to Experda's customers.

How to Reset SA Password In SQL Server Using Single-User Mode?

1. Open Command Prompt in administrator mode:

Type in “Command Prompt”

Right-click

Click “Run as administrator”

Click “Yes” when prompted

2. Stop SQL Instance from running:

Type in “net stop SQL Server (<Instance>)

Press “Enter”

This will stop the currently running SQL Services.

3. Restart SQL in Single-User Mode

Type in “net start MSSQLSERVER -m”SQLCMD”

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.

4. Connect to SQL:

Type in “sqlcsd”

Press “Enter”

Doing so opens the SQL command line

5. Create a new user and password: You’ll do this with typed commands in the SQL command line:

Type in “create login <Login Name> with password=<’password’> (where “name” is the account name and “password” is the new password

Press “Enter”

Type in “go”

Press “Enter”

6. Add the user to the System Administrator role:

Type in “SP_ ADDSRVROLEMEMBER name, ‘SYSADMIN’” where “name” is the account name

Press “Enter”

Type in “GO”

Press “Enter”

7. Exit the SQLCMD command line:

Type in “Exit”

Press “Enter”

8. Restart SQL in regular mode:

Cancel Single-User mode by typing in

net stop MSSQLSERRVER

Press “Enter”

net start SQL Server (instance)

Press “Enter”

You should see the “The SQL Server <MSSQLSERVER> service was started successfully” phrase appear again

At this point, you can close Command Prompt

9. Open SSMS:

Type SQL server management studio into Start

Click Microsoft SQL Server Management Studio 17 at the top of the Start

10. Elect the correct authentication:

Click the “Authentication”

Drop-down box

Click SQL Server Authentication in the menu

11. Log in with the new user’s credentials:

Click the “Login”

Drop-down box

Click the name of the user you just created

12. Click “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.

13. Expand 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.

14. Expand the “security” folder: It’s below the server’s name.

15. Expand the “Logins” folder: You’ll find this in the group of options below the “Security” folder.

16. Double-click SA: It’s in the “Logins” group of options. Doing so opens your System Administrator properties window.

17. Enter a new password:

Type your new password into both the “Password” and the “Confirm password” text fields near the top of the window.

Share this post

Share on facebook
Share on linkedin
Share on twitter
Share on whatsapp
Accessibility