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.
very Well It works!thank you for sharing your information in such a good way!!!
This was EXACTLY what I needed. Thanks much!
Thank you thank you thank you
I also find other 3 methods to reset forgotten SA password, learn more details at: http://www.top-password.com/knowledge/change-sql-server-password.html
Nice job !!! Thank you !!! *****
Super! You made my day!
great how to, Thanks a lot
Thanks a lot .. it worked for me .. saved my lot of time for reinstallation
Very very useful,, this resolves my issue,,,
I was very much happy,,,,,,,
Thanks a lot for the guys who have found this…
This works (as you knew it would) Thank you.
Thank you the above article is very helpfull
This article saved a lot of time for me. Thank you
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?
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
It’s not Working properly……It’s getting Occur
Sqlcmd: Error:Sqlserver Native Client 10.0 login Timeout Expaired…
How to solve it…
Hi,
Can you send me the steps you followed and where it went wrong??
Thanks
Gert
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!
Thank you – just what I was looking for and very easy to follow.
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.
thanks a lot for the info. it save lots my time as I was planning to do reinstall itself 🙂
These instructions are the best. Thank you.
yesss .i can solve my issue. Thanks very very much
Bless your cotton socks!
THANK YOU!!!!
Merci, merci, merci, merci, merci, merci !
Beautiful work,definitely we can try for this tool to recovery the password.Many thanks for well information and share.
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!
Great – Thanks!
Thank you very much.
Nice job !!! Thank you !!!
This works for me . Thanks
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.
You are great!!!! Very Well It works!thank you for sharing your information in such a good way!!!
Thank you so much !!!!
All the best to you in your life man !!
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.
Thanks, you saved my day!
Respect! Thanks a lot!
Perfect! You made my day! Thanks!
Worked perfectly and saved the day! Thank you!
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!
Thanks a million times Derek. This one worked so well for me as I had the same problem.
Cheers
Problem solved, thank You very much!
Worked amazingly. Cheers!
You need to run command prompt with administrative rights.
@Rafik Berzi
Thank you so much, I was getting authentication failures even though I was logged in with admin credentials. This got me in.
Thanks a lot brow. It’s worked..Great Tutorial 🙂
Nice post mate, helped me out just now (inherited a developer machine with no idea what the local password was!)
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.
Brillant, that was a life saver !
It’s working fine.Thanks..
After adding -m; the “SQLCMD” command worked for me when I started only the “SQL Server (MSSQLSERVER)” service.
Hi,
It was so complete and useful for me.
Thanks a lot
Superb!!!!!!!! Thanks from my heart…………..
This solution also works for SQL Server 2012. Thanks a lot!
Thank you man. Its great.
Looks like this does not work for 2012 🙁 bummer – or I am missing something?
not sure, not yet tested on SQL 2012
Gert
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!
I am using Traction PRO as theme
Gert
tnx a lot
Awesome. Cheers!
thanks a lot!!!!!!!!!!
Thank you, very effective with easy to follw instructions!
Nearly 2 years later and it’s still helping people! Thanks.
Thank you very Much
In the line sp_addsrvrolemember ‘recovery’, ‘sysadmin’
I had to write sp_addsrvrolemember “recovery”, “sysadmin”
even so it works very well
sp_addsrvrolemember ‘recovery’, ‘sysadmin’ also threw an error, but the following worked for me:
sp_addsrvrolemember recovery, sysadmin
Thanks again to the writer!
Thanks!
Great, it’s really helpful thanks
Saved my ass…
Wow! This could not have been written any better! Thanks so much!
Thanks a million, this fixed things right up.
You’re my hero !
Thank you so much !
And it is very very clear. Very good job.
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!
It Workzz !! Cheers 🙂
Worked great Thank you !
This saved me a lot of trouble!!!
Thanks for sharing.
Excellent, thank you
thanx a lot. 😉
it’s work , Thank so much
Thanks,
You’re my hero !
With SQL Server 2008 R2 Express I had to use:
sqlcmd -S [servername]\[instance]
This was even for the default instance sqlexpress.
Good job
YOU ARE THE MAN!!! saved me
Great Job. Thank you!
Thanks a lot, you save me
Thanks a lot!!!!!
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)
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
Thanks a lot, very clearly explained 🙂
thank you!
still helps in 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
Thanks! Very, very helpful!
The instructions are very clear and straight forward. Thanks!
Excellent article. Really got me out of a bind. thank you.
thanks, it works like a charm
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.
http://www.sqlmdfviewer.org/sql-password-recovery-freeware.html
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 !
Dal
There no tool that can do this
Superb! Very simple and straightforward to follow.