Recovering a SQL Server instance connection

Original: Restore SQL Server Instance connection

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 turned on, the local Administrators group no longer needs to be added manually because it is added by default at the time of installation. You even need to manually add the current user to the local administrator user. This means, especially if you do not use the hybrid authentication mode (or forget the sa password), you may be denied login to your own SQL Server instance. I met a situation where an employee left, but his Windows account, which only had SQL Server administrator privileges, would completely block the system. 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 will require a certain amount of downtime. For some systems, this is unacceptable. And depending on the needs of the Management Server, there may be no feasible plan to wait for the server to be maintained.

A more extreme approach is to shut down SQL Server, copy all the mdf/ldf files, install a new SQL Server instance, and attach all the user databases. In addition to downtime, you will have to re-create the system databases, logins, connection servers, jobs, and other elements outside the database to make the system reusable. Also, unless you uninstall the old instance, the new instance needs to use a named instance, which causes all client applications to follow the upgrade.

Solution:

Use the following methods can be painless, non-stop to solve the above problem: PsExec. But this is not its main design goal. It allows you to run the program as an NT AUTHORITY\SYSTEM account, unlike the "regular" Administrator account – with the ability to access SQL Server natively.

The use of this process is very simple. Log on to SQL Server running servers and have the account with local administrator privileges. Download and open PsExec.exe. Open as Administrator. Execute the following commands and pay attention to the actual path of your SQL Server Management Studio:

Psexec-s-I "C:\Program Files (x86) \microsoft SQL Server\110\tools\binn\managementstudio\ssms.exe"

The command tells PsExec to run SSMs as administrator (-s) and interactive (-i).

If you are not an administrator, an error message is returned. You must agree to the license terms to continue the operation. When you log in to SSMs, you are prompted to connect to the server. You need to be aware that you are logged in with Windows Authentication mode, and that the user name is hard-coded, such as: NT Authority\System:

When you are logged in, expand Object Explorer and you can see that you are connecting with NT AUTHORITY\SYSTEM:

Now, you can go to "security" → "login name" and then add your account as sysadmin. Add other admin accounts, modify the sa password, and other things that will keep your instances in a manageable state.

Warning:

You must pretend to be a local administrator of Ntauthority\system. You must disable UAC. You must be able to operate remotely, but I haven't tested it since I focused on the virtual machine.

If you change the sa password. and external programs use SA account, you need to upgrade these programs. Refer to: A tip for updating the password used in maintenance plans. Use CTRL + C to exit the command line.

Conclusion:

PsExec can solve a lot of problems that make you headache. It is also desirable to add enough administrators when installing SQL Server to reduce the occurrence of these problems. Extended reading:

• Auditing SQL Server Permissions and Roles for the Server
• Server level permissions for SQL Server 2005 and SQL Server 2008
• When is the last time the SQL Server sa password changed?
• Get back to SQL Server after your ' ve Locked yourself out
• How to connect to SQL Server if you is completely locked out

Recovering a SQL Server instance connection