Skip to content

May 26, 2011


How to recover SA password on Microsoft SQL Server 2008 R2

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.
Share and Enjoy:
  • LinkedIn
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
Read more from General, Security, SQL
100 Comments Post a comment
  1. bahare
    Sep 19 2011

    very Well It works!thank you for sharing your information in such a good way!!!

  2. Craig Martin
    Feb 10 2012

    This was EXACTLY what I needed. Thanks much!

  3. Stephen Miller
    Mar 1 2012

    Thank you thank you thank you

  4. Sancho
    Mar 4 2012

    I also find other 3 methods to reset forgotten SA password, learn more details at:

  5. Mar 10 2012

    Nice job !!! Thank you !!! *****

  6. Fredrik
    Mar 15 2012

    Super! You made my day!

  7. Mar 28 2012

    great how to, Thanks a lot

  8. Gaurav
    Mar 29 2012

    Thanks a lot .. it worked for me .. saved my lot of time for reinstallation

  9. Harish
    Mar 30 2012

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

    Thanks a lot for the guys who have found this…

  10. JRH
    Apr 5 2012

    This works (as you knew it would) Thank you.

  11. Apr 11 2012

    Thank you the above article is very helpfull

  12. Apr 13 2012

    This article saved a lot of time for me. Thank you

  13. T
    Apr 14 2012

    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?

    • Diego R.
      Apr 25 2012


      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 .\
      sqlcmd -S \

      sqlcmd-S HOMEPC\SQLSERVER2

      More about it at

  14. Apr 21 2012

    It’s not Working properly……It’s getting Occur
    Sqlcmd: Error:Sqlserver Native Client 10.0 login Timeout Expaired…

    How to solve it…

    • gertvangorp
      Apr 24 2012


      Can you send me the steps you followed and where it went wrong??



    • Dez
      Nov 14 2012

      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!

  15. Kaz
    Apr 23 2012

    Thank you – just what I was looking for and very easy to follow.

  16. Dennis
    May 15 2012

    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.

  17. Sanjay
    May 26 2012

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

  18. Paul
    Jun 11 2012

    These instructions are the best. Thank you.

  19. Heby Joseph
    Jun 14 2012

    yesss .i can solve my issue. Thanks very very much

  20. tristan
    Jun 22 2012

    Bless your cotton socks!

    THANK YOU!!!!

  21. Vali
    Jun 27 2012

    Merci, merci, merci, merci, merci, merci !

  22. Jul 30 2012

    Beautiful work,definitely we can try for this tool to recovery the password.Many thanks for well information and share.

  23. MonsterCable
    Aug 8 2012

    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!

  24. Jeep
    Aug 28 2012

    Great – Thanks!

  25. modtanoy
    Sep 5 2012

    Thank you very much.

  26. Sep 11 2012

    Nice job !!! Thank you !!!

  27. Ashok Tewatia
    Sep 19 2012

    This works for me . Thanks

  28. Cornel
    Sep 28 2012

    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.

  29. Pedro
    Sep 29 2012

    You are great!!!! Very Well It works!thank you for sharing your information in such a good way!!!

  30. Fabien
    Oct 4 2012

    Thank you so much !!!!
    All the best to you in your life man !!

  31. XiaoYu
    Oct 5 2012

    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.

  32. Roberto
    Oct 12 2012

    Thanks, you saved my day!

  33. Lothar Geisinger
    Oct 22 2012

    Respect! Thanks a lot!

  34. Sam
    Oct 26 2012

    Perfect! You made my day! Thanks!

  35. Hacked
    Oct 29 2012

    Worked perfectly and saved the day! Thank you!

  36. Nov 2 2012

    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!

    • Teezee
      Aug 27 2013

      Thanks a million times Derek. This one worked so well for me as I had the same problem.


  37. AldoArmisi
    Nov 6 2012

    Problem solved, thank You very much!

  38. Matt
    Nov 7 2012

    Worked amazingly. Cheers!

  39. Nov 14 2012

    You need to run command prompt with administrative rights.

    • Jimmy
      Sep 10 2013

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

  40. Sunan
    Nov 15 2012

    Thanks a lot brow. It’s worked..Great Tutorial :)

  41. Nov 26 2012

    Nice post mate, helped me out just now (inherited a developer machine with no idea what the local password was!)

  42. Jerry
    Nov 28 2012

    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.

  43. tskeggs
    Dec 14 2012

    Brillant, that was a life saver !

  44. Rajesh
    Dec 15 2012

    It’s working fine.Thanks..

  45. SQLn00b
    Dec 17 2012

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

  46. Ali
    Jan 9 2013

    It was so complete and useful for me.

    Thanks a lot

  47. Suday Kumar Ghosh
    Jan 20 2013

    Superb!!!!!!!! Thanks from my heart…………..

  48. Saskia
    Jan 21 2013

    This solution also works for SQL Server 2012. Thanks a lot!

  49. Kumaresan
    Feb 11 2013

    Thank you man. Its great.

  50. Shane
    Feb 27 2013

    Looks like this does not work for 2012 :( bummer – or I am missing something?

    • gertvangorp
      Aug 16 2013

      not sure, not yet tested on SQL 2012


  51. Mar 13 2013

    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!

    • gertvangorp
      Aug 16 2013

      I am using Traction PRO as theme


  52. Amin Reza
    Mar 17 2013

    tnx a lot

  53. Jortx
    Mar 21 2013

    Awesome. Cheers!

  54. kannan.g
    Mar 26 2013

    thanks a lot!!!!!!!!!!

  55. Stevek
    Apr 3 2013

    Thank you, very effective with easy to follw instructions!

  56. Andy
    Apr 24 2013

    Nearly 2 years later and it’s still helping people! Thanks.

  57. Rakesh Kumar Singh
    May 22 2013

    Thank you very Much

  58. pele
    May 29 2013

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

    even so it works very well

    • Dec 17 2013

      sp_addsrvrolemember ‘recovery’, ‘sysadmin’ also threw an error, but the following worked for me:
      sp_addsrvrolemember recovery, sysadmin

      Thanks again to the writer!

  59. Ali Azsari
    Jun 19 2013


  60. paresh
    Jun 27 2013

    Great, it’s really helpful thanks

  61. Kake
    Jul 8 2013

    Saved my ass…

  62. Sheila
    Jul 17 2013

    Wow! This could not have been written any better! Thanks so much!

  63. Jul 26 2013

    Thanks a million, this fixed things right up.

  64. Christiane
    Aug 1 2013

    You’re my hero !
    Thank you so much !
    And it is very very clear. Very good job.

  65. 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!

  66. Perry
    Aug 8 2013

    It Workzz !! Cheers :-)

  67. Florin Chirilas
    Aug 14 2013

    Worked great Thank you !

  68. Aug 20 2013

    This saved me a lot of trouble!!!

    Thanks for sharing.

  69. Juan
    Aug 20 2013

    Excellent, thank you

  70. Teezee
    Aug 27 2013

    thanx a lot. ;)

  71. Remez
    Aug 29 2013

    it’s work , Thank so much

  72. masi
    Aug 31 2013

    You’re my hero !

  73. MB
    Sep 19 2013

    With SQL Server 2008 R2 Express I had to use:

    sqlcmd -S [servername]\[instance]

    This was even for the default instance sqlexpress.

  74. Mirek
    Sep 26 2013

    Good job

  75. kemal kayalı
    Sep 28 2013

    YOU ARE THE MAN!!! saved me

  76. Ronaldo Bicca
    Oct 4 2013

    Great Job. Thank you!

  77. Rajaonary
    Oct 9 2013

    Thanks a lot, you save me

  78. shahp
    Oct 15 2013

    Thanks a lot!!!!!

  79. Oct 17 2013

    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)

    • Oct 17 2013

      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

  80. Oct 23 2013

    Thanks a lot, very clearly explained :)

  81. fipsinator
    Oct 30 2013

    thank you!
    still helps in 2013 :-)

  82. Ross
    Oct 31 2013

    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

  83. Peter
    Nov 1 2013

    Thanks! Very, very helpful!

  84. Khalid
    Nov 21 2013

    The instructions are very clear and straight forward. Thanks!

  85. Andrew
    Dec 11 2013

    Excellent article. Really got me out of a bind. thank you.

  86. Rudy
    Dec 19 2013

    thanks, it works like a charm

  87. Jan 17 2014

    this process is very complicated i have another solution to recover the SQL Server Password which is highly reliable and robust the SQL Password recovery software. You can use its demo version which ensured you how it perform riskfree password recovery process.

  88. Jun 17 2014

    I do not want to change the SQL SA password as many applications explicitly depend on this password such as Sun finance system , Ifor etc.
    Is there a tool that can show the the password !


    • gertvangorp
      Sep 5 2014

      There no tool that can do this

  89. sid
    Aug 1 2014

    Superb! Very simple and straightforward to follow.


Trackbacks & Pingbacks

  1. Lost SA Password for SQL 2008 R2 | Squishnet

Leave a comment


Note: HTML is allowed. Your email address will never be published.

Subscribe to comments