Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

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

MSSQLTips author Manvendra Singh By:   |   Read Comments (6)   |   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: 4/26/2012


About the author
MSSQLTips author Manvendra Singh
Manvendra Signh 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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, January 22, 2013 - 3:09:30 PM - Adam Read The Tip

Brilliant! Thanks a lot!! :)


Friday, April 27, 2012 - 7:50:13 AM - Shamas Read The Tip

This is very good post for DBA's.


Thursday, April 26, 2012 - 12:35:49 PM - Calin OPREA Read The Tip

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 Read The Tip

Neat and clever.

Cheers


Thursday, April 26, 2012 - 7:31:46 AM - Ashok Read The Tip

Good One..!

Thanks ..


Thursday, April 26, 2012 - 6:26:13 AM - BalaKrishna K Read The Tip

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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.