Restoring access to the SQL Server instance without restarting

Preamble

​​

Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the installation, this can only be done manually.

As a result, if the administrator’s group was not added during or after installation, there are situations where administrative access to SQL Server is lost. This may happen when the user from under which the installation was performed is unknown or the password of the same account is lost, or when the previous database administrator quit and his Windows account, being the only one with administrator rights for SQL Server, was deleted.

Usually, in this case, the regular mechanism with a single-user SQL Server restart is used. However, this approach requires some downtime, and in some systems this is unacceptable.

There is a very painless way to solve this problem without any downtime if certain conditions are met. Many have heard and used Mark Russinovich’s utilities – Windows Sysinternals. This set contains the PsExec utility, which allows you to start a process on a local or remote machine.

One of the advantages of this utility is the ability to run processes on behalf of an NT AUTHORITY\SYSTEM account, which – unlike “normal” administrator accounts – has access to SQL Server by default.

For this method to work, SQL Server must have Windows Authentication enabled and run as an NT account AUTHORITY\SYSTEM, otherwise, the NT account AUTHORITY\SYSTEM will only have public rights, not a sysadmin. 

Preparing to restore administrative access to SQL Server

1. Make sure the SQL Server service is running on behalf of your NT account AUTHORITY\SYSTEM.
2. Download and install the current version of SQL Server Management Studio (SSMS) on your SQL server, for example here
3. Download and unpack the archive to SQL server the current version of PsTools utility set (PsExec is included into it), for example, PSTools.

Additions to SQL Server Administrator

1. Run the command line with elevated rights on the SQL server (Shift + right click, “Run as administrator”) and navigate to the folder where PsExec utility is unpacked.
2. Perform the command below, correcting your actual path to Management Studio:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Settings:

• -i – indicates that it is necessary to start the process in interactive mode
• -s – indicates that you need to run the process on behalf of your NT system account AUTHORITY\SYSTEM.
  You will get approximately the following (at the first launch you will see a window with the license agreement):

and the SSMS will start.

3 Clicking Connect will allow you to connect and see the list of objects on the server.

4. Now, go to Security > Logins and add your account as a system administrator, and update the same password, in general, do everything necessary to make your instance manageable.

Logins and add your account as a system administrator, and update the sa password, in general, do everything necessary to make your instance manageable” width=”300″ height=”160″ title=””>

This method, although it has a number of limitations, but allows you to restore SQL server management without stopping the database server.

Connect SQL Server Instance without Using management Studio – using SqlCmd

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.