SQL Server 2000
- Login to Server using Local Administrator user
- Login to Query Analyzer using Windows Authentication.
- Use this procedure to change the SA password:sp_password null,’newpassword’,sa
SQL Server 2005
Based on this article:
- Open the Configuration Manager tool from the “SQL Server 2005| Configuration” menu
- Stop the SQL Server Instance you need to recover
- Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
- 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.”
- 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’;
- 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