Friday, February 25, 2011

How to reset SQL Server SA Password

The problem is that you don’t know the password for the SQL Server SA account, and for whatever reason the Windows account you are using is being told you don’t have permissions to change the SA account properties.  If you can't login to Management Studio using Windows Authentication this will not work for you.

First, you need to get the name of the SQL Server Service that is running, do this by clicking on Start and running services.msc.

Scroll to SQL Server (name), there may be more than one, if so, get the information for all of them if you are not certain which is the instance for the account you need to reset. Double-click on the line and record the Service name.

Open a command prompt and enter:
          NET STOP [Service name]
          NET START [Service name] /m
This will start the service in single user mode, which will allow you to make changes to the SA user account.

Start SQL Server Management Studio and login to the instance using Windows authentication. Go up to File and close the object explorer. Now click on New Query and enter this code:

          USE [master]
          GO
          ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master],
          DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
          GO
          USE [master]
          GO
          ALTER LOGIN [sa] WITH PASSWORD=N’password’ MUST_CHANGE
          GO

In the code above where it says “password” you will enter your new password. You may have to change this again later so be prepared with two options for what you want your final password to be. Now execute the code. If there were no errors, exit from Management Studio and stop and restart the service:
          NET STOP [Service name]
          NET START [Service name]

Go back into Management Studio and login with the SA account using the new password you assigned. WARNING, you may get prompted to change the password, if so proceed with what will be the new final password.

One time when I did this I still had a problem because the SA account was not enabled. After doing the above, I had to create a new login that I gave sysadmin rights to that I then used to enable the SA account. I then deleted that temporary login. If I remember right, I did this while still in single user mode.

You may need to go back into Services and manually start dependent services that were shutdown when you first stopped the SQL Service.

Credits to the two sites that provided me with the help I needed to accomplish this:
http://deepakrangarajan.blogspot.com/2008/01/forgot-sa-password-in-sql-server-2005.html
http://worthposting.wordpress.com/2008/11/01/sql-server-reset-sa-password/

Labels: , , , , ,

3 Comments:

At June 8, 2011 at 9:34 PM , Blogger michelle said...

Forgot MS SQL Server SA password? SmartKey SQL Password Recovery is a professional MS SQL Server password recovery utility that can easily help to reset lost or forgotten MS SQL Server SA password in minutes without data loss.No matter how long and complex your SA passwords are, SQL Password Recovery can 100% guaranteed to instantly reset user and administrator Passwords for Microsoft SQL Server 2000/2005/2008

Source:http://recoverlostpassword.com/products/sqlpasswordrecovery.html

 
At June 15, 2011 at 9:19 PM , Anonymous PeterPatrickGo said...

Very great post. I learnt a lot from u. Tks 4 sharing.

 
At March 1, 2012 at 8:49 AM , Anonymous jame said...

I also find a good trick to reset forgotten SA password, check out at:
http://www.top-password.com/knowledge/reset-sa-password-from-command-prompt.html

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home