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.

24 Responses to “SQL Server 2005: How to Reset a Forgotten Password for the sa Account”

  1. December 24, 2008 at 1:54 pm, João Cavaleiro said:

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

    Reply

    • December 30, 2009 at 9:09 am, Jay said:

      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

      Reply

    • April 30, 2011 at 11:31 am, Jamil said:

      Great very useful…….

      Reply

  2. January 20, 2009 at 5:21 am, Sathish said:

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

    Reply

  3. May 22, 2009 at 10:48 am, vijay said:

    Its very useful for me.

    Reply

  4. July 30, 2009 at 9:53 am, Anonymous said:

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

    Reply

  5. June 17, 2010 at 7:29 am, Anonymous said:

    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

    Reply

  6. July 26, 2010 at 6:25 am, Shirantha said:

    Thanks dude..

    Reply

  7. July 27, 2010 at 2:00 pm, Shockvolt said:

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

    Reply

  8. August 30, 2010 at 2:39 pm, Harshavardhan said:

    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.

    Reply

  9. December 01, 2010 at 6:59 am, Anil Kumar Sahoo said:

    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???

    Reply

  10. May 17, 2011 at 12:16 pm, PTG said:

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

    Reply

  11. July 06, 2011 at 6:16 am, Vikas said:

    yes it works thanx dude

    Reply

  12. August 05, 2011 at 9:49 am, Jayant said:

    Really Amazing, better one.. Thanks Buddy

    Reply

  13. August 18, 2011 at 5:12 am, Spark Ms said:

    The above script is excellent. Thanks dear.

    Reply

  14. April 05, 2012 at 11:55 am, friend said:

    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

    Reply

    • April 21, 2012 at 3:35 pm, ANJI Varikuti said:

      Ya !

      Reply

  15. April 21, 2012 at 3:35 pm, ANJI Varikuti said:

    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

    Reply

    • November 26, 2012 at 6:21 pm, claud'as cnj said:

      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

      Reply

  16. July 08, 2012 at 6:26 am, Baljeet Yadav said:

    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.

    Reply

    • August 03, 2012 at 9:48 am, Amit Pawar said:

      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’

      Reply

  17. August 07, 2012 at 6:35 am, kalpana said:

    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.

    Reply

  18. January 29, 2013 at 10:12 am, Dawn said:

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

    Reply

  19. August 19, 2013 at 2:49 am, uma said:

    it display some error

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

    Reply

Leave a Reply