How to Reset SA Password in SQL Server

PDF download Download Article
Reset your SA password in SQL with this easy-to-follow guide
PDF download Download Article

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.

Method 1
Method 1 of 3:

Using Windows Authentication

PDF download Download Article
  1. 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]
  2. 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
  3. Type sql server management studio into Start, then click Microsoft SQL Server Management Studio 17 at the top of the Start window.
  4. Click the "Authentication" drop-down box, then click Windows Authentication in the menu.
  5. 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. 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. It's below the server's name.
  8. You'll find this in the group of options below the "Security" folder.
  9. It's in the "Logins" group of options. Doing so opens your System Administrator properties window.
  10. Type your new password into both the "Password" and the "Confirm password" text fields near the top of the window.
  11. It's at the bottom of the window. Doing so will change your password and close the properties window.
    12. Advertisement
Method 2
Method 2 of 3:

Using Single-User Mode

PDF download Download Article
  1. 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. 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. Open Start
    Windows Start
    , then do the following:
    • Type in command prompt
    • Right-click
      Windows cmd
      Command Prompt
    • Click Run as administrator
    • Click Yes when prompted.
  4. Type in net stop MSSQLSERVER and press ↵ Enter . This will stop the currently running SQL services.
  5. 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.
  6. Type in sqlcmd and press ↵ Enter . Doing so opens the SQL command line.
  7. 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 .
  8. Type in SP_ADDSRVROLEMEMBER name,’SYSADMIN’ where "name" is the account name, press ↵ Enter , and then type in GO and press ↵ Enter .
  9. Type in exit and press ↵ Enter .
  10. 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.
  11. Type sql server management studio into Start, then click Microsoft SQL Server Management Studio 17 at the top of the Start window.
  12. Click the "Authentication" drop-down box, then click SQL Server Authentication in the menu.
  13. Click the "Login" drop-down box, then click the name of the user you just created.
  14. Type the user's password into the "Password" text box near the bottom of the window.
  15. 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. 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. It's below the server's name.
  18. You'll find this in the group of options below the "Security" folder.
  19. It's in the "Logins" group of options. Doing so opens your System Administrator properties window.
  20. Type your new password into both the "Password" and the "Confirm password" text fields near the top of the window.
  21. It's at the bottom of the window. Doing so will change your password and close the properties window.
    22. Advertisement
Method 3
Method 3 of 3:

Using Command Prompt

PDF download Download Article
  1. Click the Windows logo in the bottom-left corner of the screen. This will open the Start menu.
  2. Type in command prompt , then wait for Command Prompt to appear at the top of the Start menu.
  3. Doing so will prompt a drop-down menu.
  4. It's in the drop-down menu.
  5. This will confirm your decision to open Command Prompt in administrator mode. The Command Prompt window should open.
  6. Type in osql -L and press ↵ Enter . [3]
  7. Type in OSQL -S server -E where "server" is replaced by your server's name, then press ↵ Enter .
  8. 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.
  9. Type in GO , then press ↵ Enter . Type exit , then press ↵ Enter to exit OSQL.
  10. 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.
    11. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • Should none of these methods work for your situation, there are several paid SQL Server password recovery programs available online.
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      Advertisement

      You Might Also Like

      The Ultimate Guide to Using Microsoft Access
      How to
      Learn Data Entry
      How to
      Make a Database Using MS Access
      2 Ways to Import Data from the Web into Excel: Windows & Mac
      How to
      Add Data to a Pivot Table
      How to
      Order Alphabetically in SQL
      How to
      Make an Inventory Database in Access: Complete Guide
      Everything You Need to Know About Removing Duplicate Records in Oracle
      How to
      Link Tables in Access
      How to
      Create a Database from an Excel Spreadsheet
      How to
      Download Microsoft Access
      How to
      Hack a Database
      How to
      Write Basic SQL Statements in SQL Server
      How to
      Check SQL Server Query Performance: Step-by-Step Guide
      Advertisement

      References

      1. ↑ http://www.itprotoday.com/microsoft-sql-server/q-how-can-i-recover-my-sa-password
      2. ↑ https://www.experts-exchange.com/articles/27901/How-to-Reset-a-Lost-SA-Password-in-Microsoft-SQL-Server.html
      3. ↑ https://sysinfotools.com/blog/reset-sql-server-password/

      About This Article

      Article Summary X

      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 .

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 1,158,523 times.

      Is this article up to date?

      Advertisement