SQL Server database forgot sa account password/no admin account solution

One, the computer Super Administrator account has the database administrator rights

Log in to the database with the Administrator account and modify the SA account password directly.

Ii. accounts without administrator privileges in the database

SQL Server 2005/2008 provides a better approach to disaster recovery for this situation, eliminating the need to hack into the master database and not cause damage to objects and data in the master database, such as login accounts, certificates, and so on. Members of the Windows Administrators group now have access to SQL Server when SQL Server starts in single-user mode (another name is Maintenance mode).

By using single-user mode, SQL Server 2005/2008 avoids the abuse of permissions by the Windows administrator as a sysadmin without being discovered. A 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 the appropriate control over file access and other permissions. If you use the Configuration management tool to recover your system, you can follow these steps:

1. From the menu->sql server 2005-> Configuration, open the Configuration Management tool (SQL Server Configuration Manager)

2. Stop the instance of SQL Server you want to recover

3. Find the "Advanced" tab, "–m" in the "Startup Parameters" option text box.

4. Click the "Yes" button to restart the SQL Server instance

Please note: Make sure that the ";" There is no space between and "-M". The registration parameter parser is very sensitive to similar input errors. You can see the "SQL Server started in Single-user mode" displayed 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 login account to the sysadmin server role or to create a new login account. The example statements are as follows:

EXEC sp_addsrvrolemember ' contoso\buck ', ' sysadmin ';

GO

6. This restores the database administrator permissions for the Super administrator of the computer. Restart the SQL Server instance by removing the "; M" parameter from the startup parameter in configuration management as long as the sysadmin access is restored

Important Safety Tips:

A. The process should only be used when there is no other way to access a system's disaster recovery using a privileged account, such as a sysadmin or equivalent role.

B. This procedure allows Windows administrators to have ultra vires behavior in SQL Server. It contains a clear intrusion behavior that can be monitored and detected, including:

1. Stop SQL Server and restart in single-user mode

2. Connect to SQL Server using a Windows account.

SQL Server database forgot sa account password/no admin account solution