Remedy for forgetting the Administrator Password

If you accidentally forget the SQLServer administrator password (that is, all administrator passwords are forgotten), or you need to forcibly add another administrator account, you need a remedy. SQLServer provides a single user mode

If you accidentally forget the SQLServer administrator password (that is, all administrator passwords are forgotten), or you need to forcibly add another administrator account, you need a remedy. SQLServer provides a single user mode

If you accidentally forget the SQL Server administrator password (that is, all administrator passwords are forgotten), or you need to forcibly add another administrator account, you need a remedy.

SQL Server provides a single-user mode (also known as the maintenance mode), allowing you to change Server configuration options or recover damaged system databases. When SQL Server is started in single-user mode, any member of the local Administrators Group can be connected as a member of the sysadmin Server role to the SQL Server database engine (instance ).

Note: Before starting in single-user mode, stop the SQL Server Agent service to prevent the SQL Server Agent from occupying this unique connection.

1. Enable single-user mode

1. Open SSCM (SQL Server Configuration Manager)

2. Stop the SQL Server engine service (Instance)

3. Modify the attributes of the engine service

4. Add single-user mode startup parameters

(1) applicable to earlier versions of SQL Server 2012

(2) applicable to SQL Server 2012 and later versions

5. After completing the preceding modification, start the Database Engine service.

6. Check the startup log and confirm that you have entered the single-user mode.

13:55:08. 95 spid7s SQL Server started in single-user mode. This an informational message only. No user action is required.

Note: The location and file name of the startup log file are specified by the "-e" parameter of "Startup parameter. For example:

C: \ Users \ Administrator> notepad "C: \ Program Files \ Microsoft SQL Server \ MSSQL10_50.SQL2008R2 \ MSSQL \ Log \ ERRORLOG"

2. Unauthorized addition or modification of administrator accounts

1. view the Database Engine service list (ensure that the Instance name is not entered incorrectly during the next operation)

C: \ Users \ Administrator> sqlcmd-L

Server:
PC2014
PC2014 \ SQL2008R2
PC2014 \ SQL2012

2. Open the command line window and use sqlcmd to connect to the database

C: \ Users \ Administrator> sqlcmd-S PC2014 \ SQL2008R2

3. execute one of the following T-SQL statements to add or modify accounts

(1) Add local or domain accounts to the Database Administrator Group

1> EXEC sp_addsrvrolemember 'pc2014 \ Jim', 'sysadmin ';
2> GO

(2) Add a built-in account to the Database Administrator Group

1> EXEC sp_addsrvrolemember 'builtin \ administrators ', 'sysadmin ';
2> GO

(3) If you forget the old password, reset the password (sp_password is not officially recommended)

1> Alter Login [BUILTIN \ administrator] with password = 'newpassword ';
2> GO

(4) If you still remember the old password, change it.

1> Alter Login [sa] with password = 'newpassword' old_password = 'oldpassword ';
2> GO

Note: If the SA account is disabled

1> Alter Login [sa] ENABLE;
2> GO

Note: If the Server Authentication mode is only "Windows Authentication Mode", you must change it to "SQL Server and Windows Authentication Mode" to use the sa account. To modify this mode, you need to modify the registry. The registry key is HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft SQL Server \ <实例id> \ MSSQLServer, change LoginMode to 2.

The default name format of "instance ID" is "MSSQL ". <版本号> . <实例名称> ", Such as" MSSQL12.MSSQLSERVER ". This setting is specified during SQL Server installation. The installation wizard uses this "instance ID" to create the corresponding folder.

3. Start the Database Engine service

1. Open SSCM, modify the database engine attributes, and remove the "-m" parameter from the startup parameter.

2. Start the Database Engine service to connect to the database with a new or modified database administrator account.

Note: You can use the command line to start the single-user mode.

1. Find the sqlservr.exe path.

2. Copy sqlservr.exe to the command line window, add the "-m" parameter, and run

For example:

C: \ Users \ Administrator> "C: \ Program Files \ Microsoft SQL Server \ MSSQL10_50.SQL2008R2 \ MSSQL \ Binn \ sqlservr.exe"-sSQL2008R2-m

The startup log is directly displayed in the command line window. Check the startup log and confirm that the single user mode is enabled successfully.

Note: You can also forcibly specify the client program.

(1)-m "sqlcmd ". Only sqlcmd can be connected to the SQL Server instance.

(2)-m "Microsoft SQL Server Management Studio-Query ". Specify that only SSMS can connect to the SQL Server instance.

3. In addition, you can use sqlcmd to run the command line window.

4. If you need to stop running SQL Server, just press ctrl-c(very close the sqlservr.exe window directly) IN THE sqlservr.exe window, and then answer "Y" when you have a question ".

Do you wish to shutdown SQL Server (Y/N )?

This article is from the "MSSQL we have Chased Together" blog. Please keep this source