I just received a call from an old friend with whom I used to work in Las Vegas. He told me about a password-related issue he faced in his organization. They had changed the password of username SA and now they are not able to recall the new password. I am sure that he is not the first person who has faced this issue. There may be many more similar situations where employees who have sysamin password leaves the job or a hacker disables the SA account.
Resetting the password of SA is a breeze!
Option 1 :
If there is any other SQL Server Login that is a member of sysadmin role, you can log in using that account and reset the password of SQL Server. Change the password of SA account as described here : SQL SERVER – Change Password of SA Login Using Management Studio.
Option 2 :
If there is any other Windows Login that is a member of Windows Admin Group, log in using that account. Start SQL Server in Single User Mode as described here : SQL SERVER – Start SQL Server Instance in Single User Mode.
Create a new login and give it sysadmin permission.
Note : If you have SQL Server Agent enabled, it starts before SQL Server service. If you have enabled SQL Server in a single user mode, it will connect it first, so it is recommended to turn that off before attempting any of the above options.
Reference : Pinal Dave (https://blog.sqlauthority.com)
43 Comments. Leave new
Is there any difference sql management studo R1 and R2
hi pinal,
I face a problem of Login failed again and again in sql server authentication for sql server r2 2008……..
Try this
sp_password @new = ‘123’, @loginame = ‘sa’
Couple of notes for my situation to get it working for SQL Server 2008R2
I used option #2 detailed above, and followed the link to setting up in single user mode, and had a slight modification to the startup parameter
-m;-dC:\Program Files….. (basically adding the – in front of dC, as opposed to screenshot)
Also, I had to stop all the other SQL server services (Sql agent, reporting, etc…) as they would give me an error if they were still running when I tried to connect via sqlcmd.
Another thing was that when clicking on the command prompt, I right clicked it and selected run as Administrator and ran the following command: (Replacing Domain\Alex_Martinez with your desired user name of course)
EXEC sp_addsrvrolemember ‘Domain\Alex_Martinez’, ‘sysadmin’;
GO
QUIT
At this point I was able to login with the new sysadmin role above and change the SA password via Mangement Studio. Hope this helps another newbie like myself.
sqlsever login is locked out..
how to unlock
I am a newbie at SQL server administration and this is great! Thanks!
Thanks :)
passkey -m query -u sa
perfect, just what i was looking for
Pinal you saved my day today . You are awesome
this server block my website plz help guy
It’s awesome in favor of me to have a web site, which is valuable designed for my experience. thanks admin
awesome blog !!!
Happy to see people like you who tries to share what ever they learnt.
Thanks !
Thanks! I thought I’d be reinstalling SQL and re-attaching datasbases. This article saved me a bunch of time. Couldn’t have been easier!
Hi,
To change the SQL sa password from a command prompt:
Start a command prompt by typing Start – Run – cmd
Enter the following commands, pressing Enter after each line
OSQL -S yourservername -E
1> EXEC sp_password NULL, ‘yourpassword’, ‘sa’
2> GO
Where yourservername is the name of your server and yourpassword is the new sa account password. Type exit twice to return to the server desktop.
This works in SQL 2005. You can try with versions above Sql 2005
Sir,
my question is if the situation is where window authentication mode is disabled and their is only a single login account sa and in this condition we forgot password than how can we recover password and gain access to the account.
I prefer to use the third-party software – SQL Server Password Changer. It make password changing easier.
Brian – Are they free?
Very helpful, thank you!