HomeProgramDatabase

How to Reset SA Password in Sql Server

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. Understand how this...
Method 1 of 3:

Using Windows Authentication

  1. How to Reset SA Password in Sql Server Picture 1How to Reset SA Password in Sql Server Picture 1
    Understand 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]
    1. If Windows Authentication isn't enabled, you'll need to either use Single-User Mode or use Command Prompt to reset your password.
  2. How to Reset SA Password in Sql Server Picture 2How to Reset SA Password in Sql Server Picture 2
    Make 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:
    1. Go to the SSMS installation page in a browser.
    2. Click the Download SQL Server Management Studio 17.6 link.
    3. Double-click the downloaded SSMS setup file.
    4. Follow the on-screen prompts to install SSMS.
  3. How to Reset SA Password in Sql Server Picture 3How to Reset SA Password in Sql Server Picture 3
    Open SSMS. Type sql server management studio into Start, then click Microsoft SQL Server Management Studio 17 at the top of the Start window.
  4. How to Reset SA Password in Sql Server Picture 4How to Reset SA Password in Sql Server Picture 4
    Select the correct authentication. Click the "Authentication" drop-down box, then click Windows Authentication in the menu.
  5. How to Reset SA Password in Sql Server Picture 5How to Reset SA Password in Sql Server Picture 5
    Click 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.
  6. How to Reset SA Password in Sql Server Picture 6How to Reset SA Password in Sql Server Picture 6
    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.
  7. How to Reset SA Password in Sql Server Picture 7How to Reset SA Password in Sql Server Picture 7
    Expand the "Security" folder. It's below the server's name.
  8. How to Reset SA Password in Sql Server Picture 8How to Reset SA Password in Sql Server Picture 8
    Expand the "Logins" folder. You'll find this in the group of options below the "Security" folder.
  9. How to Reset SA Password in Sql Server Picture 9How to Reset SA Password in Sql Server Picture 9
    Double-click sa. It's in the "Logins" group of options. Doing so opens your System Administrator properties window.
  10. How to Reset SA Password in Sql Server Picture 10How to Reset SA Password in Sql Server Picture 10
    Enter a new password. Type your new password into both the "Password" and the "Confirm password" text fields near the top of the window.
  11. How to Reset SA Password in Sql Server Picture 11How to Reset SA Password in Sql Server Picture 11
    Click OK. It's at the bottom of the window. Doing so will change your password and close the properties window.
Method 2 of 3:

Using Single-User Mode

  1. How to Reset SA Password in Sql Server Picture 12How to Reset SA Password in Sql Server Picture 12
    Understand 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]
  2. How to Reset SA Password in Sql Server Picture 13How to Reset SA Password in Sql Server Picture 13
    Make 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:
    1. Go to the SSMS installation page in a browser.
    2. Click the Download SQL Server Management Studio 17.6 link.
    3. Double-click the downloaded SSMS setup file.
    4. Follow the on-screen prompts to install SSMS.
  3. How to Reset SA Password in Sql Server Picture 14How to Reset SA Password in Sql Server Picture 14
    Open Command Prompt in administrator mode. Open Start
    How to Reset SA Password in Sql Server Picture 15How to Reset SA Password in Sql Server Picture 15
    , then do the following:
    1. Type in command prompt
    2. Right-click
      How to Reset SA Password in Sql Server Picture 16How to Reset SA Password in Sql Server Picture 16
      Command Prompt
    3. Click Run as administrator
    4. Click Yes when prompted.
  4. How to Reset SA Password in Sql Server Picture 17How to Reset SA Password in Sql Server Picture 17
    Stop SQL Instance from running. Type in net stop MSSQLSERVER and press ↵ Enter. This will stop the currently running SQL services.
  5. How to Reset SA Password in Sql Server Picture 18How to Reset SA Password in Sql Server Picture 18
    Restart SQL in Single-User Mode. Type in net start MSSQLSERVER -m"SQLCMD" and press ↵ Enter.
    1. 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 service was started successfully" appear.
  6. How to Reset SA Password in Sql Server Picture 19How to Reset SA Password in Sql Server Picture 19
    Connect to SQL. Type in sqlcmd and press ↵ Enter. Doing so opens the SQL command line.
  7. How to Reset SA Password in Sql Server Picture 20How to Reset SA Password in Sql Server Picture 20
    Create a new user and password. You'll do this with typed commands in the SQL command line:
    1. Type in CREATE LOGIN name WITH PASSWORD='password' where "name" is the account name and "password" is the new password.
    2. Press ↵ Enter.
    3. Type in GO and press ↵ Enter.
  8. How to Reset SA Password in Sql Server Picture 21How to Reset SA Password in Sql Server Picture 21
    Add 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.
  9. How to Reset SA Password in Sql Server Picture 22How to Reset SA Password in Sql Server Picture 22
    Exit the SQLCMD command line. Type in exit and press ↵ Enter.
  10. How to Reset SA Password in Sql Server Picture 23How to Reset SA Password in Sql Server Picture 23
    Restart SQL in regular mode. You can cancel Single-User Mode by typing in net stop MSSQLSERVER && net start MSSQLSERVER and pressing ↵ Enter.
    1. You should see the "The SQL Server service was started successfully" phrase appear again; at this point, you can close Command Prompt.
  11. How to Reset SA Password in Sql Server Picture 24How to Reset SA Password in Sql Server Picture 24
    Open SSMS. Type sql server management studio into Start, then click Microsoft SQL Server Management Studio 17 at the top of the Start window.
  12. How to Reset SA Password in Sql Server Picture 25How to Reset SA Password in Sql Server Picture 25
    Select the correct authentication. Click the "Authentication" drop-down box, then click SQL Server Authentication in the menu.
  13. How to Reset SA Password in Sql Server Picture 26How to Reset SA Password in Sql Server Picture 26
    Log in with the new user's credentials. Click the "Login" drop-down box, then click the name of the user you just created.
  14. How to Reset SA Password in Sql Server Picture 27How to Reset SA Password in Sql Server Picture 27
    Enter the password. Type the user's password into the "Password" text box near the bottom of the window.
  15. How to Reset SA Password in Sql Server Picture 28How to Reset SA Password in Sql Server Picture 28
    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.
  16. How to Reset SA Password in Sql Server Picture 29How to Reset SA Password in Sql Server Picture 29
    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.
  17. How to Reset SA Password in Sql Server Picture 30How to Reset SA Password in Sql Server Picture 30
    Expand the "Security" folder. It's below the server's name.
  18. How to Reset SA Password in Sql Server Picture 31How to Reset SA Password in Sql Server Picture 31
    Expand the "Logins" folder. You'll find this in the group of options below the "Security" folder.
  19. How to Reset SA Password in Sql Server Picture 32How to Reset SA Password in Sql Server Picture 32
    Double-click sa. It's in the "Logins" group of options. Doing so opens your System Administrator properties window.
  20. How to Reset SA Password in Sql Server Picture 33How to Reset SA Password in Sql Server Picture 33
    Enter a new password. Type your new password into both the "Password" and the "Confirm password" text fields near the top of the window.
  21. How to Reset SA Password in Sql Server Picture 34How to Reset SA Password in Sql Server Picture 34
    Click OK. It's at the bottom of the window. Doing so will change your password and close the properties window.
Method 3 of 3:

Using Command Prompt

  1. How to Reset SA Password in Sql Server Picture 35How to Reset SA Password in Sql Server Picture 35
    Open Start
    How to Reset SA Password in Sql Server Picture 36How to Reset SA Password in Sql Server Picture 36
    . Click the Windows logo in the bottom-left corner of the screen. This will open the Start menu.
  2. How to Reset SA Password in Sql Server Picture 37How to Reset SA Password in Sql Server Picture 37
    Search for Command Prompt. Type in command prompt, then wait for Command Prompt to appear at the top of the Start menu.
  3. How to Reset SA Password in Sql Server Picture 38How to Reset SA Password in Sql Server Picture 38
    Right-click
    How to Reset SA Password in Sql Server Picture 39How to Reset SA Password in Sql Server Picture 39
    Command Prompt. Doing so will prompt a drop-down menu.
  4. How to Reset SA Password in Sql Server Picture 40How to Reset SA Password in Sql Server Picture 40
    Click Run as administrator. It's in the drop-down menu.
  5. How to Reset SA Password in Sql Server Picture 41How to Reset SA Password in Sql Server Picture 41
    Click Yes when prompted. This will confirm your decision to open Command Prompt in administrator mode. The Command Prompt window should open.
  6. How to Reset SA Password in Sql Server Picture 42How to Reset SA Password in Sql Server Picture 42
    Enter the first command. Type in osql -L and press ↵ Enter.[3]
  7. How to Reset SA Password in Sql Server Picture 43How to Reset SA Password in Sql Server Picture 43
    Enter 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.
  8. How to Reset SA Password in Sql Server Picture 44How to Reset SA Password in Sql Server Picture 44
    Create 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.
    1. For example, to set your password as "rutabaga123", you would type EXEC sp_password NULL, 'rutabaga123', 'sa' into Command Prompt.
  9. How to Reset SA Password in Sql Server Picture 45How to Reset SA Password in Sql Server Picture 45
    Execute the command. Type in GO, then press ↵ Enter. Type exit, then press ↵ Enter to exit OSQL.
  10. How to Reset SA Password in Sql Server Picture 46How to Reset SA Password in Sql Server Picture 46
    Attempt 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.
4 ★ | 3 Vote
passwordserverclicksql

You should read it

May be interested

Database