How to connect to SQL Server if you are completely locked out

By:   |   Comments (10)   |   Related: > Security

Suppose you are a DBA responsible for managing all of the SQL Server instances in your environment. For the sake of security you lock down your environment by removing the BUILTIN\Administrators group, disable sa the login, change the port number, removed all users except sa that were part of SYSADMIN server role. Now let's assume during the locked down process, all SYSADMIN accesses has been revoked from your instance.  In this case you can successfully connect to the SQL Server instance, but you cannot perform any SYSADMIN tasks. How can you grant SYSADMIN access to manage your SQL Server instance? 

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

1 - First stop the SQL Server and SQL Serve Agent services in SQL Server Management Studio

2 - Open a cmd prompt window (Start | Run | cmd and press the 'OK' button) and navigate to SQL Server's Binn directory as shown below.

Stop the SQL Server Services

3 - 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.

Start SQL Server in Single User Mode

Starting SQL Server could take a few seconds to complete. Once your SQL Server instance has been started in single user mode, the following lines will be shown at the end of cmd prompt session:

SQL Server recovered after starting in Single User Mode

4 - Now open a separate command prompt window (Start | Run | cmd and press the 'OK' button) and connect to your SQL Server instance by using the sqlcmd utility.  If you have a named instance then use the '-S' parameter with full instance name. The '-E' switch is used for a trusted connection.  If you are a Windows local Administrator on the machine, you will be granted SQL Server SYSADMIN rights when you connect to SQL Server in single user mode.  This is the key to the access in order for this process to work.

Run SQLCMD with a trusted connection.

5 - Now you are connected to your SQL Server instance with SYSADMIN rights. At this point you can add the BUILTIN\Administrators group back to the instance, create a login with SQL Server SYSADMIN rights or you can add your DBA Windows domain group as a SQL Server SYSADMIN.

Create a SQL Server Login in SQLCMD

6 - Assign the SQL Server sysadmin role to your newly created login:

Add SQL Server sysadmin rights to the new login

7 - Now start the SQL Server services in SQL Server Management Studio and connect to the instance using the newly created login.

8 - Here you can validate the SQL Server sysadmin access for your new login i.e. mssqltips. 

Review all of the SQL Server sysadmins on the server
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, May 28, 2019 - 12:57:48 PM - Teri Back To Top (81236)

I tried this, but when I login to the SQL Server, I get the message: 

Cannot connect to SERVER.

Additional information:

-> A connection was succesfully established with the server, but then an error occurred during the

      login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the 

      pipe.) (Microsoft SQL Server, Error: 233)

     -> No Process is on the other end of the pipe

Please help! I'm running SQL Server 14, installed on my workstation for development purposes. Highers-up decided to consolidate users in a single active directory container, so my login credentials changed. My old login had full sysAdmin rights, but, apparently, I didn't get them all attributed to my new login, and now I only have read access, having prematurely removed my old login. And, of course, I hadn't backed up my database recently. UGH.

Wednesday, February 8, 2017 - 12:01:06 AM - Manvendra Back To Top (46082)



As error suggests that you have multiple SQL Server instances hosted on your box. Please pass correct instance name with parameter -S"InstanceName" along with sqlcmd cmd.

Thursday, February 2, 2017 - 11:05:26 AM - smt Back To Top (45809)

I am getting below messge. Can someone help?

2017-02-02 11:01:21.22 Server      Multiple instances of SQL server are installe

d on this computer. Renter the command, specifying the -s parameter with the nam

e of the instance that you want to start.

2017-02-02 11:01:21.24 Server      SQL Server shutdown has been initiated

Wednesday, August 12, 2015 - 12:14:25 PM - ematia Back To Top (38433)

Thank you. Worked like a champ

Tuesday, January 22, 2013 - 3:09:30 PM - Adam Back To Top (21625)

Brilliant! Thanks a lot!! :)

Friday, April 27, 2012 - 7:50:13 AM - Shamas Back To Top (17152)

This is very good post for DBA's.

Thursday, April 26, 2012 - 12:35:49 PM - Calin OPREA Back To Top (17134)

You can also try this: ; nixe post

Thursday, April 26, 2012 - 8:32:13 AM - Mark Ma Back To Top (17123)

Neat and clever.


Thursday, April 26, 2012 - 7:31:46 AM - Ashok Back To Top (17121)

Good One..!

Thanks ..

Thursday, April 26, 2012 - 6:26:13 AM - BalaKrishna K Back To Top (17119)

Thanks a lot... I got this problem .. and tried with this solution... It's working fine.

get free sql tips
agree to terms