When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

There can be different reasons you lost the password

  • Person who installed the SQL Server knows the password but has left the building.
  • You did not write down the password in your password file
  • Password file is lost
  • …

Steps to recover the SA password

  • Start SQL Server Configuration Manager

  • Stop the SQL services

  • Edit the properties of the SQL Service

  • Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

  • Start the SQL services. These are now running in Single User Mode.
  • Start CMD on tthe SQL server
  • Start the SQLCMD command. Now you will see following screen

  • Now we create a new user. Enter following commands
    • CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1’ (Remember SQL server has default strong password policy
    • Go
      • Now this user is created
  • Now we grant the user a SYSADMIN roles using the same SQLCMD window.
    • sp_addsrvrolemember ‘recovery’, ‘sysadmin’
    • go
  • Stop the SQL service again
  • Change the SQL service properties back to the default settings

  • Start the SQL service again and use the new created login (recovery in my example)
  • Go via the security panel to the properties and change the password of the SA account.

  • Now write down the new SA password. لعبة دومينو اون لاين للكمبيوتر
By General, Security, SQL97 Comments on How to recover SA password on Microsoft SQL Server 2008 R2

97 Replies to “How to recover SA password on Microsoft SQL Server 2008 R2”

  9. Very very useful,, this resolves my issue,,,
    I was very much happy,,,,,,,

    Thanks a lot for the guys who have found this…

    Reply

  13. Thanks for the step by step details, this helped a lot with the default instance. Would you know how to make it do it for a named instance? I have searched and searched by no one seems to be plagued by a named instance. So maybe I’m missing something really obvious?

    Reply

    1. Hey,

      I was having the same problem but finally found a solution, now I just can’t login, i’ve been able to create the user but somehow I keep getting a Login failed for user ‘recovery’ (Even when copying user and password straight from the console statement)

      sqlcmd-S .\
      or
      sqlcmd -S \

      e.g.
      sqlcmd-S HOMEPC\SQLSERVER2

      More about it at
      http://msdn.microsoft.com/en-us/library/ms188247.aspx

      Reply

    2. Had to use:

      SQLCMD -E -S ‘server_name\db_name’

      to solve “Sqlcmd: Error:Sqlserver Native Client 10.0 login Timeout Expired” error. Worked terrific after that, thank you!

      Reply

  16. Wanted to say BIG THANK YOU!!! for super easy instruction that work well. Unlike other site they lure people using techniques that not going to work and giving us option to purchased software ect.

    Reply
  17. Pingback: Lost SA Password for SQL 2008 R2 | Squishnet

  18. thanks a lot for the info. it save lots my time as I was planning to do reinstall itself 🙂

    Reply

  24. Thank you so much! After searching and searching online, this was exactly what I was looking for. I guess most people don’t understand what it means to lose the SQL SA password.

    Thanks again!

    Reply

  29. Thank you!
    Had to use:
    sqlcmd -S
    othewise it would not connect to any instance. Also if you cut and paste from this web page, re-type the single quotes to avoid syntax errors.

    Reply

  32. This is great, well written step by step instruction. THANK YOU. One more unrelated question, how did you make the screen shot looks like a broken paper edge? Thank you again.

    Reply

  37. Thanks for the awesome solution!

    Just a few tips:

    Since you’re in single-user mode, you may receive the following error:

    “Login failed for user ‘[user]’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461).

    To resolve this, make sure you stop SSAS and SSRS and SQL Server Agent for the instance that you’re working with. You can then get in.

    Also, if you have direct access to the server, you’ll be able to login to SQL Management Studio (if installed) and then do your management through the GUI.

    For my case, I needed to get my network account to authenticate on my local PC, which was being denied because someone else installed SQL on my machine. I was part of the Administrators group, so it let me take it over.

    Happy coding!

    Reply

    1. @Rafik Berzi
      Thank you so much, I was getting authentication failures even though I was logged in with admin credentials. This got me in.

      Reply

  43. It works.

    a few advise

    1. this password recovery is for single login mode, not mixed mode(as mentioned at very begining). if you SQL setup in mixed mode, you can reset your sa password using OS admin account.

    2.Derek’s tip very useful. I had same situation.

    Reply

  46. After adding -m; the “SQLCMD” command worked for me when I started only the “SQL Server (MSSQLSERVER)” service.

    Reply

  52. I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more enjoyable for me to come
    here and visit more often. Did you hire out a developer to create your theme?
    Fantastic work!

    Reply

  59. In the line sp_addsrvrolemember ‘recovery’, ‘sysadmin’
    I had to write sp_addsrvrolemember “recovery”, “sysadmin”

    even so it works very well

    Reply

  66. Hi excellent website! Does running a blog similar to this take a great
    deal of work? I’ve virtually no knowledge of programming but I had been hoping to start my own blog in the near future. Anyway, if you have any ideas or techniques for new blog owners please share. I know this is off topic however I just wanted to ask. Many thanks!

    Reply

  74. With SQL Server 2008 R2 Express I had to use:

    sqlcmd -S [servername]\[instance]

    This was even for the default instance sqlexpress.

    Reply

  80. This didn’t work for me.

    It tells me “Alter failed for Login ‘sa’

    An exception occurred while executing a Transact-SQL statement or batch.

    Cannot set a credential for principal ‘sa’ (Microsoft SQL Server, Error:15535)

    Reply

    1. found a solution, had to do the following:

      select the “Map To Credential” check box on the General tab of the Login Properties – sa dialog box.

      that got rid of my error

      Reply

  83. Worked but only after doing something else, not mentioned in the article above…

    Starting SQLCMD without any other parameters only starts it in the default instance of SQL, if you are trying to create an SA account in a named instance (e.g. sqlServer\sql2008), you need to connect SQLCMD to that named instance. Mine for example, is \SQL2008 so to connect SQLCMD to that, I had to open the cmd prompt and write:
    SQLCMD -S .\SQL2008

    Reply

Leave a Reply to paresh Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.