[Translation] What should I do when the SA account is lost?

Q: I encountered a disaster: I lost the SA password of SQL Server 2005. Is there a way to recover it? I followed the best security practices and deleted the builtin \ Administrators account from sysadmin. No one else is sysadmin. Is there any other method besides re-installing SQL Server and attaching the database? This may cause data corruption in the master database.

A:
SQL Server 2005/2008 provides a better disaster recovery method for this situation. It does not need to intrude into the master database and will not cause damage to objects and data in the master database (such as logon accounts and certificates. Members in the Windows Administrator group now have access permissions to SQL Server when SQL Server is started in single-user mode (another is named maintenance mode.

In single-user mode, SQL Server 2005/2008 prevents the Windows administrator from abusing permissions without being discovered as sysadmin. The Windows Administrator account can perform some maintenance tasks, such as installing patches.

To start SQL Server in single-user mode, you can add the "-m" parameter in command line mode. You can also use the SQL Server configuration administrator tool. It provides proper control over file access and other permissions. If you use the configuration management tool to restore your system, follow these steps:

1. Choose SQL Server 2005> Configuration from the menu to open the configuration management tool.
2. Stop the SQL Server instance you want to restore
3. Find the "advanced" tab and add ";-m" to the "startup parameters" option at the end of the attribute text box"
4. Click "yes" to restart the SQL Server instance.
Note: make sure there is no space between ";" and "-m. The register parameter parser is very sensitive to similar input errors. You can see "SQL Server started in single-user mode." in the SQL Server ERRORLOG file ."
5. After the SQL Server instance is started in single user mode, the Windows Administrator account can use the sqlcmd tool to connect to SQL Server in Windows Authentication mode. You can use T-SQL commands such as "sp_addsrvrolemember" to add an existing Logon account or create a new Logon account in the sysadmin server role. The example statement is as follows:

EXEC sp_addsrvrolemember' CONTOSO \ Buck ', 'sysadmin ';

GO

6. If the access permission of sysadmin is restored, remove the ";-m" parameter from the startup parameters in configuration management and restart the SQL Server instance.

Important security tips:
A. This process should only be used for disaster recovery when no other method uses privileged accounts (such as sysadmin or equivalent roles) to access the system.
B. This process allows the Windows administrator to perform unauthorized operations on SQL Server. It includes clear intrusion behaviors that can be monitored and detected, including:
1. Stop SQL Server in single-user mode and restart
2. Use a Windows Account to connect to SQL Server