Remedy for forgetting the Administrator Password

Source: Internet
Author: User
Tags builtin

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.