The remedy for forgetting the administrator password

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

SQL Server provides Single-user mode (also known as maintenance mode) to make it easy for users to change server configuration options or restore compromised system databases. Starting SQL Server in Single-user mode enables any member of the native Administrators group to connect to the SQL Server database engine (instance) as a member of the sysadmin server role.

Note: Before you start Single-user mode, stop the SQL Server Agent service to prevent the SQL Server Agent from seizing this unique connection.

First, enable single user mode

1. Open SSCM (SQL Server Configuration Manager)

2. Stop SQL Server Engine service (instance)

3. Modify the properties of the engine service

4. Add single user mode boot parameters

(1) applicable to older versions prior to SQL Server 2012

(2) applicable to SQL Server 2012 and back-land versions

5. After completing the above modifications, start the Database engine service

6. Check the startup log to confirm that you have entered Single-user mode

2014-10-13 13:55:08.95 spid7s SQL Server started in Single-user mode. This is informational. No user action is required.

Note: the location and filename of the startup log file is specified by the "-E" parameter of the startup parameter. For example:

C:\users\administrator> Notepad "C:\Program Files\Microsoft SQL Server\mssql10_50.sql2008r2\mssql\log\errorlog"

Second, ultra vires add or modify Administrator account

1. View the list of Database engine services (ensure that the name of the instance is not misspelled when the next step is done)

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 an account number

(1) Add local or domain accounts to the Database Administrators group

1> EXEC sp_addsrvrolemember ' pc2014\jim ', ' sysadmin ';
2> Go

(2) Add built-in accounts to the Database Administrators group

1> EXEC sp_addsrvrolemember ' BUILTIN\Administrators ', ' sysadmin ';
2> Go

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

1> Alter Login [builtin\administrator] with password= ' newpassword ';
2> Go

(4) If you remember the old password, modify the password

1> Alter Login [sa] with password= ' newpassword ' old_password= ' OldPassword ';
2> Go

Note: If the SA account is disabled, the

1> Alter Login [SA] ENABLE;
2> Go

Note: If the server authentication mode is only Windows Authentication mode, you will need to change to SQL Server and Windows Authentication mode to use the SA account. Modifying this mode requires modifying the registry, which is located at: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL server\< instance id>\mssqlserver, Change the LoginMode to 2.

The name format of the instance ID defaults to "mssql< version number >.< instance Name >", for example, "MSSQL12." MSSQLServer ". This setting is specified when you install SQL Server, and the Setup wizard will use this instance ID to create the corresponding folder.

Third, start the Database engine service

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

2. Start the Database engine service and connect to the database with the newly created or modified database administrator account.

Note: Ways to start Single-user mode directly using the command line

1. Find the path to Sqlservr.exe

2. Copy sqlservr.exe to the Command line window, add "-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 will appear directly in the Command Line window, check the startup log to verify that Single-user mode has been successfully enabled.

Note: You can also force the client program to be specified.

(1)-M "sqlcmd". Specifies that only sqlcmd can connect to an instance of SQL Server.

(2)-M "Microsoft SQL Server Management studio-query". Specifies that only SSMS can connect to an instance of SQL Server.

3. Open a command line window, that is, you can use SQLCMD to perform operations.

4. To stop an instance of SQL Server, simply press ctrl-c in the Sqlservr.exe window (or even close the Sqlservr.exe window) and answer "Y" when you encounter a question.

Do your wish to shutdown SQL Server (y/n)?

This article is from the "MSSQL We've chased" blog, make sure to keep this source http://jimshu.blog.51cto.com/3171847/1563207