IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
You are here: Home / Database / How to reset SA password on Microsoft SQL Server

How to reset SA password on Microsoft SQL Server

If you happen to forget your sa password, you can still recover it as long as you have access to the server. Here are the steps to do it.

Steps

  1. Launch Sql Configuration Manager under Configuration Tools folder.

    Sql Server Configuration Manager Menu

  2. Look for your SQL Server instance (the default is MSSQLSERVER) and stop the service. You can click the stop button while having the SQL Server (MSSQLSERVER) row highlighted or you can right-click on it and select Stop.

    Screenshot of Sql Server Configuration Manager

  3. Launch the Command Prompt.

  4. Next, we want to run the SQL Server in a single-user mode by adding “/m” parameter with the client application name:
    net start MSSQLSERVER /m"SQLCMD"

  5. Then we need to connect to the database on the machine using a trusted connection:
    sqlcmd -E -S localhost

    If you’re connecting to a database on a local machine, you can substitute “localhost” with a “.” (dot), which makes it look like so:
    sqlcmd -E -S . or sqlcmd -E -S.

    The two are identical except the former is easier to read. Another note is if you’re using SQL Server Express, you need to add “\SQLEXPRESS” after the period. You can see the difference on the example below.

  6. After starting the sqlcmd, type the following SQL statement after the prompt. There is a difference in role assignment between SQL Server 2008 and SQL Server 2012:

    For SQL Server 2008 or Older

                    CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                    GO
                    sp_addsrvrolemember 'tempUser', 'sysadmin'
                    GO
                    

    Create temp login on SQLCMD for SQL Server 2008

    For SQL Server 2012 or Later

                    CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                    GO
                    ALTER SERVER ROLE sysadmin ADD MEMBER tempUser
                    GO
                    

    For SQL Server 2012 or newer, use ALTER SERVER ROLE should be used instead of sp_addsrvrolemember as this system stored procedure will be removed in a future version of Microsoft SQL Server.

    Create temp login on SQLCMD for SQL Server Express 2012

    You can type “exit” to quit SQLCMD.

  7. Restart the Sql Server service to get out of the single-user mode:
    net stop MSSQLSERVER followed by net start MSSQLSERVER

    Restart MSSQL Server service via CMD

  8. Launch SQL Server Management Studio and connect to the local database using the new login you just created.

    Connect to SQL Server using SSMS

  9. Expand on Security, then expand on Logins.

    Highlighted sa user on Object Explorer (SSMS)

  10. Right-click on user sa and select Properties. Enter the new password and click OK. And you’re done.

    Right-click on sa to check Properties

Now you can login to the database using the sa login and the new password you set. For security purpose, make sure you delete the tempUser afterwards.

Further Reading

ALTER SERVER ROLE (Transact-SQL)
T-SQL Fundamentals (3rd Edition)

January 23, 2015 Filed Under: Database, How To Tagged With: Microsoft SQL Server, SQL, SQL Server

Comments

  1. Scott says

    January 20, 2020 at 12:54 pm

    It works! Thank you very much!

    Reply
    • platt says

      January 20, 2020 at 1:21 pm

      You’re welcome Scott!

      Reply
  2. Javier Iglesias says

    December 19, 2017 at 12:29 pm

    Amazing!
    Muchas gracias. Fue de gran ayuda para mi. Logre desbloquear sin problemas mi usuario SA.

    Reply
  3. syro says

    April 20, 2017 at 9:39 am

    Exelente POST 🙂

    Reply
  4. Marian says

    June 8, 2015 at 8:08 am

    Great tips! I usually use the SQL Server Password Changer utility as it works with all editions of SQL Server.

    Reply
    • platt says

      June 23, 2015 at 9:30 pm

      Hi Marian, thanks for the tip. I’ve never heard about that and it looks pretty good. But with the US$50, I have to be a DBA or someone who deals with this often enough to justify the price tag. 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Buy me a coffee Support this site
Buy Me a Coffee?

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • Tips and Tricks
  • Use Case
  • WordPress
  • Writing

Recent Posts

  • How to Check Installed .NET Framework Version
  • How to Remove .NET Runtime and SDK on Mac
  • How to Solve Intermittent 403 Error in IIS
  • How to Show Hidden Folders and Files in Mac Finder
  • How to Solve MS Office VBA Compile Error UserAuthentication

Recent Posts

  • How to Check Installed .NET Framework Version
  • How to Remove .NET Runtime and SDK on Mac
  • How to Solve Intermittent 403 Error in IIS
  • How to Show Hidden Folders and Files in Mac Finder
  • How to Solve MS Office VBA Compile Error UserAuthentication
  • RSS

Tags

.NET Access AdSense ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack Linux macOS Microsoft Microsoft SQL Server MVC PHP Python Simple Mail Transfer Protocol Smtp Server Social Media SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Windows Windows 8 Windows 10 Windows Server

Copyright © 2011-2022 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure