Restoring access to the SQL Server instance without restarting it

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.

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

--

--

--

We conduct reviews, guides and comparative tests of gaming laptops, monitors, graphics cards, keyboards, mouses, headsets and chairs to help you buy the best ga

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Java Series: Basics Q&A Part 5

Top 6 Advantages To Learn Data Science with Python

Some updated DigiByte mining stats graphs / comparisons

Using a Data Vault to Support Data Science

Measuring the Impact: How to Use Data to Optimize Software Delivery

Hack This Site: Extended Basic — Mission 4

Hack This Site: Extended Basic — Mission 4

Running ReactJs, NextJS, and Deploying on my iPad

Why do you need global gitignore file

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Akademily

Akademily

We conduct reviews, guides and comparative tests of gaming laptops, monitors, graphics cards, keyboards, mouses, headsets and chairs to help you buy the best ga

More from Medium

SQL Query optimization using Statistics in OutSystems

AEM : Advancing workflow after exception in a custom process step

sample-workflow

Patient Data Access in Clinical Trials

EXCEPTION handling for transaction with Snowflake Scripting(Stored Procedure/Anonymous block)