Connect to SQL Server when you don’t know SA account or don’t have sysadmin access

Below are the steps you need to perform to grant SYSADMIN access to a user in SQL Server in case you are completely locked out.

1. Download PSexec to connect using SQL Server Management Studio using the NT Authority\System

Download PsTools from https://download.sysinternals.com/files/PSTools.zip
Unzip the content and copy PsExec.exe to C:\Windows\System32

2. Stop the SQL Server and SQL Server Agent services on the server.

3. Open a cmd prompt window as administrator and navigate to SQL Server’s Binn directory. You may need to adjust your path based on your install location.

ex. C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn

4. Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below. Had to add the location of the ERRORLOG

sqlservr -m -e C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG;

If it’s a named instance:
sqlservr -m -s <instancename> -e C:\Program Files\Microsoft SQL Server\MSSQL11.SERVICECORE\MSSQL\Log\ERRORLOG;

After the SQL Server instance was started in single user mode, I was receiving logon errors form other users that were trying to logon from the application, when it was restarted in single user mode. I ignored the errors and moved on.

5. Execute PsExec – may need to adjust the ” marks and file locations

run cmd as administrator

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

The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM

6. Click Connect and then go in to Security > Logins and add your account as a sysadmin

7. restart the sql server services

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *