How to Recover the SA Password


SQL Server 2000

  1. Login to Server using Local Administrator user
  2. Login to Query Analyzer using Windows Authentication.
  3. Use this procedure to change the SA password:sp_password null,’newpassword’,sa

SQL Server 2005

Based on this article:

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

  1. Open the Configuration Manager tool from the “SQL Server 2005| Configuration” menu
  2. Stop the SQL Server Instance you need to recover
  3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
  4. Click the “OK” button and restart the SQL Server Instance NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”
  5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as “sp_addsrvrolemember” to add an existing login (or a newly created one) to the sysadmin server role.
    The following example adds the account “Buck” in the “CONTOSO” domain to the SQL Server “sysadmin” role:

    EXEC sp_addsrvrolemember ‘CONTOSO\Buck’, ‘sysadmin’;
    GO

  6. Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

Important Security Notes:

This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.

This process allows a Windows Administrator account to override their privileges within SQL Server. It requires explicit and intrusive actions that can be monitored and detected, including:

  • Stop SQL Server and restart it in single use mode
  • Connecting to SQL Server using Windows credentials
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: