Www. mssqltips. comsqlservertip2682recover-access-to-a-sql-server-instance? Utm_sourcedailynewsletterutm_mediumemailutm_contentheadlineutm_campaign2012829 problem: After enabling SQLServer2008, you do not need to manually add the local administrator group because
Translated from: http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance? Utm_source = dailynewsletterutm_medium = emailutm_content = headlineutm_campaign = 2012829 problem: After SQLServer2008 is enabled, you do not need to manually add the local administrator group because
Translated from:
Http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance? Utm_source = dailynewsletter & utm_medium = email & utm_content = headline & utm_campaign = 2012829
Problem:
After SQLServer2008 is enabled, you do not need to manually add the local administrator group because it is added by default during installation. You only need to manually add the current user to the local administrator user. This means that, especially if you do not use the mixed Authentication Mode (or forget the sa password), you may be denied to log on to your SQLServerInstance. I have encountered a situation where a clerk leaves, but his windows account only has the SQL server administrator privilege, and the system is completely blocked. Of course, only this person knows the sa password. Being a local administrator or even a domain administrator will not help you.
A typical solution is to restart SQLServer in single-user mode. However, this requires a certain period of downtime. This is unacceptable for some systems. Depending on the needs of server management, there may be no feasible plan to wait for server maintenance.
One more extreme way is to disable SQLServer, copy all MDF/LDF files, and install the new SQLServerInstanceAnd attach all user databases. In addition to the downtime, you also need to re-create the system database, Login Name,ConnectionElements other than servers, jobs, and other databases can be reused by the system. In addition, unless you uninstall the oldInstanceOtherwise, the newInstanceName requiredInstanceAll client applications must be upgraded.
Solution:
You can use the following method to solve the above problem without any downtime: Running xec. But this is not its main design goal. It allows you to run programs with an nt authority \ SYSTEM account. Unlike the "regular" Administrator account, this account has the inherent permission to access SQLServer.
This process is very simple to use. Log on to the server running SQL Server and have an account with local administrator permissions. Download and open unzip xec.exe. Open as administrator. Run the following command to enter the actual path of SQLServer Management Studio:
|
Export xec-s-I "C: \ Program Files (x86) \ Microsoft SQL Server \ 110 \ Tools \ Binn \ ManagementStudio \ Ssms.exe" |
The Command tells mongoxec to run SSMS as an administrator (-s) and interactive (-I.
If you are not an administrator, an error message is returned. You must agree to the license to continue the operation. After you log on to SSMS, you will be promptedConnection. You need to note that you must log on in windows Authentication mode, and the user name is hard-coded, such as: nt authority \ SYSTEM:
After you log on, expand object resource manager. You can see that you are using nt authority \ SYSTEM.Connection:
Now, you can go to Security> login name, and add your account as sysadmin. Add other management accounts, modify sa passwords, and otherInstanceIn a controllable state.
Warning:
You must pretend to be the local administrator of NTAUTHORITY \ SYSTEM. You must disable UAC. Remote Operation is required, but I have not tested it since I put my focus on virtual machines.
If you change the sa password. And the external program uses the sa account, you need to upgrade these programs. For details, see a tip for updating the password used in maintenance plans. use CTRL + C to exit the command line.
Conclusion:
PsExec can solve many headaches. You also want to add enough administrators when installing SQLServer to reduce the problem. Additional reading:
- Auditing SQL Server Permissions and Roles for the Server
- Server level permissions for SQL Server 2005 and SQL Server 2008
- When was the last time the SQL Server sa password changed?
- Get Back into SQL Server After You 've Locked Yourself Out
- How to connect to SQL Server if you are completely locked out