How to Reset a SQL Server sa Password
page 2 of 3
by Steven Smith
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 11377/ 135
Article Contents:

Step by Step

First off, you need to log into the machine as a user with local admin rights.

Next, you should open Management Studio on the local sql server machine and change the default startup condition so that it doesn't automatically launch the object explorer.  You do this by opening Tools - Options (Environment - General) and selecting something other than Open Object Explorer in the At startup: option.

For instance, you can set it to Open new query window.

We're disabling the object explorer because it will make a connection to the local database, and we're going to be setting the database to use Single User Mode.  In that mode, as the name implies, only one connection can be made to the database at a time.  If our object explorer has already used up that connection (even if it didn't authenticate correctly), then our query window won't have an available connection and will fail.  So, we're avoiding that issue. 

After making the change, close SQL Server Management Studio.

Next, stop all SQL Server services on the server.  The easiest way to do so is to open up the SQL Server Configuration Manager.

Right-click on each Running service and select 'Stop'.  When done, hit F5 or click on the Refresh icon to confirm that all services are in fact stopped.

Now open a command window as an administrator and type in the following:

NET START MSSQLSERVER /f /T3608

You should see something like this:

(Note that you can also start the service in single user mode by adding ";-m" to the Startup Parameters used by the SQL Server (MSSQLSERVER) service from Sql Server Configuration Manager.  If you do so, remember to remove the change when you're done with these steps)

It's possible that more than one service started up in response to the above command, in which case the "extra" services may use up the single connection available.  In Sql Server Configuration Manager, refresh the services, and make sure the only one running is SQL Server (MSSQLSERVER) or whatever instance it is you're trying to work with.  Stop any other services that are not already Stopped.

Now open SQL Server Management Studio and click New Query (or use the query window that opened on startup if you configured it to do so).  Type in the following:

Use master
Go
Alter login [sa] with password=N’CHOOSEPASSWORDWISELY’
Go
Alter login [sa] enable
Go

Assuming this runs without error, your sa password should now be set (hopefully to something secure).  Return the SQL Server Configuration Manager and restart the SQL Server (MSSQLServer) services, and start any other services you require.

You should now be able to return to SQL Server Configuration Manager and log in (via query or object explorer) using SQL Server authentication and the sa user account with the password you specified above.  At which point, you can add whichever windows accounts you need to have access to the server.

It's generally a best practice not to use the sa account, and in fact if you've disabled it, that's likely one of the reasons why you would need the steps described in this article.  Be sure to re-disable it once you have re-established the windows accounts you wish to use to access the database.


View Entire Article

User Comments

Title: Reset SQL Server Password   
Name: Richard
Date: 3/4/2012 10:24:36 AM
Comment:
I also find a useful article of resetting SQL Server password, check it at: http://www.top-password.com/knowledge/change-sql-server-password.html
Title: It is also prompted for crendential   
Name: Paresh Prajapati
Date: 6/29/2011 6:32:59 AM
Comment:
We are assuming sql server can not connect with windows authentication, we have only sa sysuser. If we know only one user 'sa' and we forgot password then you said as

"Now open SQL Server Management Studio and click New Query (or use the query window that opened on startup if you configured it to do so). Type in the following:"

This is also prompted for crendential.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 10/21/2014 5:28:31 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search