Remedy for forgetting the Administrator Password

If the administrator password is accidentally forgotten or another administrator account needs to be forcibly added, a remedy is required.

 

1. Enable single-user mode

1. Open sscm (SQL Server Configuration Manager)

2. Stop the Database Service

3. modify Database Engine attributes

650) This. width = 650; "Title =" SQL engine detail .png "alt =" wKioL1Q7Z-zB1lfGAAHlB-mrhOU674.jpg "src =" http://s3.51cto.com/wyfs02/M00/4C/52/wKioL1Q7Z-zB1lfGAAHlB-mrhOU674.jpg "/>

 

4. Add single-user mode startup parameters

(1) applicable to earlier versions of SQL Server

650) This. width = 650; "Title =" start parameter sql2008.png "alt =" wkiom1q7fypbirk1aajihapzxbw725.jpg "src =" http://s3.51cto.com/wyfs02/M01/4C/52/wKiom1Q7fYPBiRK1AAJiHAPzXBw725.jpg "/>

 

(2) applicable to SQL Server 2012 and later versions

650) This. width = 650; "Title =" start parameter sql2012.png "alt =" wkiol1q7ahthamijaafe6_uwims725.jpg "src =" http://s3.51cto.com/wyfs02/M02/4C/52/wKioL1Q7aHThamIJAAFe6_uwiMs725.jpg "/>

 

5. After completing the preceding modification, start the database engine.

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

2014-10-13 13:55:08. 95 spid7s SQL Server started inSingle-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 Service list

C: \ Users \ Administrator> sqlcmd-l Server: Pc2014 Pc2014 \ sql2008r2 Pc2014 \ sql2012

 

2. Use sqlcmd to connect to the database

C: \ Users \ Administrator> sqlcmd-s localhost \ sql2008r2

 

 

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

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

1> exec SP_ADDSRVROLEMEMBER 'pc2014 \ Jim', 'sysadmin ';
2> go

 

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

1> exec SP_ADDSRVROLEMEMBER 'builtin \ administrators ', 'sysadmin ';
2> go

 

(3) If you forget the old password, reset the password of the builtin \ administrator account.

1> alter login [builtin \ Administrator] with Password = 'newpassword ';
2> go

 

(4) If you still remember the old password, change the SA account password

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 must modify the registry. The registry key is HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft SQL Server \ <instance id> \ MSSQLServer. Change loginmode to 2.

The default name format of "instance id" is "MSSQL <version number>. <Instance name> ". This setting is specified when SQL Server is installed.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4C/54/wKioL1Q7imajAlrAAAMpYpIY_Wg150.jpg "Title =" id.png "alt =" wkiol1q7imajalraaampypiy_wg150.jpg "/>

 

3. Start the database

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

2. Start the database engine to connect to the database with a new or modified Administrator Account

 

 

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

1. Find the sqlservr.exe path. 

650) This. width = 650; "Title =" service detail .png "src =" http://s3.51cto.com/wyfs02/M01/4C/53/wKiom1Q7gZGjP7v5AAIxWqcEb88147.jpg "alt =" wkiom1q7gzgjp7v5aaixwqceb88147.jpg "/>

650) This. width = 650; "Title =" Service property exe.png "src =" http://s3.51cto.com/wyfs02/M02/4C/53/wKiom1Q7garQuCwKAAHZXCVviQU110.jpg "alt =" wkiom1q7garqucwkaahzxcvviqu110.jpg "/>

 

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

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

4. If you need to stop the data warehouse, 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 be sure to keep this source http://jimshu.blog.51cto.com/3171847/1563207

Remedy for forgetting the Administrator Password