Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (7)   |   Related Tips: More > Security

Problem
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? 
Solution

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


Last Update:






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.

View all my tips
Related Resources


 



More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

Thank you. Worked like a champ


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

Brilliant! Thanks a lot!! :)


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

This is very good post for DBA's.


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

You can also try this: http://sqlblog.com/blogs/argenis_fernandez/archive/2011/07/10/think-your-windows-administrators-don-t-have-access-to-sql-server-2008-by-default-think-again.aspx ; nixe post


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

Neat and clever.

Cheers


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

Good One..!

Thanks ..


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

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


Learn more about SQL Server tools