SQL SERVER – Forgot the Password of Username SA

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 :

Solarwinds

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)

Solarwinds
, ,
Previous Post
SQLAuthority News – Author Visit – Virtual Tech Days August 2009
Next Post
SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

Related Posts

43 Comments. Leave new

  • Is there any difference sql management studo R1 and R2

    Reply
  • navdeep saxena
    May 28, 2012 11:26 am

    hi pinal,
    I face a problem of Login failed again and again in sql server authentication for sql server r2 2008……..

    Reply
  • Try this

    sp_password @new = ‘123’, @loginame = ‘sa’

    Reply
  • Alex Martinez
    July 19, 2012 3:58 am

    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.

    Reply
  • sqlsever login is locked out..
    how to unlock

    Reply
  • I am a newbie at SQL server administration and this is great! Thanks!

    Reply
  • Thanks :)

    Reply
  • passkey -m query -u sa

    Reply
  • perfect, just what i was looking for

    Reply
  • Pinal you saved my day today . You are awesome

    Reply
  • this server block my website plz help guy

    Reply
  • affilorama review
    February 1, 2013 12:22 pm

    It’s awesome in favor of me to have a web site, which is valuable designed for my experience. thanks admin

    Reply
  • awesome blog !!!
    Happy to see people like you who tries to share what ever they learnt.

    Reply
  • Thanks !

    Reply
  • 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!

    Reply
  • Khuzaima Shajapurwala
    March 30, 2014 7:58 am

    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

    Reply
  • Shashank Aggarwal
    October 1, 2014 6:46 am

    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.

    Reply
  • I prefer to use the third-party software – SQL Server Password Changer. It make password changing easier.

    Reply
  • Very helpful, thank you!

    Reply

Leave a Reply

Menu