Restore SQL Server instance connection

Problem:

After enabling SQL Server 2008, 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 Hybrid Authentication Mode (or forget the sa password), you may be denied to log on to your SQL Server instance. I have encountered a scenario 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 SQL Server 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.

An even more challenging method is to disable SQL Server, copy all MDF/LDF files, install a new SQL Server instance, and attach all user databases. In addition to the downtime, you also need to re-create the system database, login name, connection server, job, and other database elements to make the system available again. In addition, unless you detach the old instance, the new instance requires a named instance, which will lead to the upgrade of all client applications.

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, which is different from the "regular" Administrator Account-you have the inherent permission to access SQL Server.

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 SQL Server 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 prompted to connect to the server. 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 connected by nt authority \ SYSTEM:

Now, you can go to Security> login name, and add your account as sysadmin. Add other management accounts, modify the sa password, and other things that make your instance controllable.

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 SQL Server to reduce the problem.