SQL Server 2005: How to Reset a Forgotten Password for the sa Account

If you’ve forgotten the sa password for your SQL server, you are probably in a panic. Fear not, for you can reset the password and once again have access to the powers of the sa account by using the query window in the Management Studio.

1. Open the SQL Server Management Studio.

2. Open a New Query.

3. Copy, paste, and execute the following:

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO

where NewPassword is the password you wish to use for the sa account.

You can now breathe easier, and once again access the sa account. Whew!

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

  • http://www.cavaleiro.org João Cavaleiro

    Great! This should be useful in the future.. or not! Maybe when i’m 70 years old ;)

    • Jay

      I have used the software —Password Genius can solve this problem easily. It can find out all passwords which you have logged in with your computer. I would recommend you to try it.
      They promise no-hassle refund if you cannot get your work done.
      Check this out:
      http://www.password-genius.com/how-to/how-to-find-out-my-lost-password.html

    • Jamil

      Great very useful…….

  • Sathish

    It’s a useful Tip. It helped me to reset the password. Thank you.

  • vijay

    Its very useful for me.

  • Anonymous

    thanks budy
    i seached something else but i got beter than that

  • Anonymous

    Thanq very much…

    I got success with this solution ..
    GO
    ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
    GO
    USE [master]
    GO
    ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
    GO

    actually i am facing problem like when i login with Sql Server Authentication..it is not accepting the password which i set it for ‘sa’ (Even it is correct password)..it is giving me Error: 18456, Severity: 14, State: 8 ..to resolve this i opened Sql Server management studio in Windows Authentication mode and i ran the above script to re-set the password ..now i can able to connect with new password..

    Thanks & Regards
    Suresh.Vadlamudi

  • Shirantha

    Thanks dude..

  • Shockvolt

    I guess this only works if the Built-in Admins exist?

  • Harshavardhan

    GO
    ALTER LOGIN [GOPINATH-PCINFLOWSQL] WITH DEFAULT_DATABASE=[master]
    GO
    USE [master]
    GO
    ALTER LOGIN [GOPINATH-PCINFLOWSQL] WITH PASSWORD=N’abc123′ MUST_CHANGE
    GO

    after executing the above query, am getting the error as below:

    Cannot alter the login ‘GOPINATH-PCINFLOWSQL’, because it does not exist or you do not have permission.

  • Anil Kumar Sahoo

    I have accidentally disabled the sysadmin role for BUILTIN/Administrator.Now I am not able to get it to the previous state.I cannot also login to sa.Can anyone provide some solution???

  • PTG

    Beautiful
    it solved my problem
    hey i liked it a lotttt
    thxxxx dude 

  • Vikas

    yes it works thanx dude

  • Jayant

    Really Amazing, better one.. Thanks Buddy

  • Spark Ms

    The above script is excellent. Thanks dear.

  • friend

    Awesome……..
    What a relief….this worked forme….the catch was what Suresh Vadlamudi suggested…that we shoudl first connect to the DB using the WINDOWS authentication and then execute the query.

    For my version of windows OS, the MUST_CHANGE option was not support in above query…..but I just deleted that clause and it worked fine.
    Your friend

    • ANJI Varikuti

      Ya !

  • ANJI Varikuti

    Gud Idea ! but Itsn’t working properly……Try this

    —————————————————————————————————————–
    ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
    GO
    USE [master]
    GO
    ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’
    GO
    ——————————————————————————————————————–
    Its working…………properly

    • claud’as cnj

      thanks a lot A V,i’v gotten a solution!!y’ar the best man,your thing works properly!!!!i should give you my sister if you were in my country!!trust me she’s so beauty!!!thank you very much

  • Baljeet Yadav

    When I execute the Code :
    GO ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
    GO
    USE [master]
    GO
    ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
    GO

    I get the following Error Msg :
    A fatal scripting error occurred.
    Incorrect syntax was encountered while parsing GO.

    • Amit Pawar

      DON’T USE THE GO KEYWORD.
      AND THEN EXECUTE THE QUERY ONE BY ONE —Ii.e

      1.>>>ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]

      2.>>>USE [master]

      3.>>>ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’

  • kalpana

    It gives me the following error but sa exists and I am using the admin login.I don’t know what’s wrong.

    Msg 15151, Level 16, State 1, Line 3
    Cannot alter the login ‘sa’, because it does not exist or you do not have permission.

  • Dawn

    Not so helpful. copy and pasted.
    Error message “incorrect syntax near ‘GO’ “

  • uma

    it display some error

    Msg 15099, Level 16, State 1, Line 6
    The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.