Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Get Back into SQL Server After You've Locked Yourself Out

MSSQLTips author K. Brian Kelley By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | More > Security
Problem

Someone, while locking down the SQL Server, removed the permissions by which the DBAs came in and administered the server. As a result, we cannot get back into SQL Server. How can we restore our access to SQL Server?  Check out this tip to find out. 

Solution

If you are working with SQL Server 2005 or above, you're in luck. If you're talking about a SQL Server 2000 server, there are some tricks, but these revolve around trying to gain access by an account that might still have rights or by finally breaking down and rebuilding the SQL Server using rebuildm.exe and restoring an older copy of the master database. As a result, let's focus on the newer versions, which have a very easy way to get in: single user mode.

While most everyone runs Microsoft SQL Server as a service, it can be run from the command line as an application. Since it can be run from the command line, you can specify certain switches that alter it's behavior. One of those switches is for single user mode. It is this switch that provides us a way back into SQL Server. You will need administrative rights over the server, however, to use this technique successfully.

First, log on to the server using an account that has administrative rights. It is important that the account you're using is in the local Administrators group at the operating system level i.e. Windows. Once you're logged on, stop SQL Server using the services applet. The easiest way to do this is to bring up Start and then, either in the search area or if you have the Run option, to enter services.msc. Another way is to manage the server and go to Services. If you aren't sure how to do this or you don't have the appropriate administrative rights, enlist a server administrator to assist you.

Once you're in Services, stop the SQL Server instance you need to get access back to.

Open up a command line prompt (if you're running Windows Vista, Server 2008, or 7, right-click and choose Run As Administrator). Go to the directory where the sqlservr.exe executable is for that instance. For SQL Server 2008R2, for instance, you'll normally find it under the C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn directory for a default instance.

Then run the following command for a default instance:

sqlservr -m

Or this command for a named instance (note the additional switch -s with no space between it and the instance name):

sqlservr -m -s<Instance Name>

You should see SQL Server start up and a lot of output that looks like the SQL Server error log scroll through on the screen. You should hopefully see a message indicating that SQL Server is ready for connections. However, if there are a lot of databases to start up, this could scroll off the screen. Once you see the messages stop, you should be able to connect to SQL Server. Make sure you are the first connection in, because as the name implies, only one connection is allowed.

Why Does This Process Give You Access to SQL Server

When SQL Server 2005 or above is started up in single user mode, SQL Server will automatically treat members of the local Administrators group for the operating system as members of the sysadmin fixed server role. This is true even if they don't normally have such rights. As a result, someone with administrative rights over the server can go in and manually re-add the groups/users and re-grant the permissions, thereby restoring access.

Once the appropriate security has been set, stop the SQL Server (the T-SQL command SHUTDOWN will do this if you're using SQLCMD or a Query Window to connect). Once SQL Server is fully down, restart it using Services and you should now be able to get in to your SQL Server instance.

Next Steps


Last Update: 3/9/2012


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, March 09, 2012 - 8:45:39 AM - Ed Read The Tip

Good article, back in the day if someone changed the SA password and we still had right to update the sys tables this use to work like a charm for fixing the SA login password.

-- CHANGE THE PASSWORD --
Declare @new varchar(255)
Declare @loginame varchar(255)
Set @new = 'Hemi4u'
Set @loginame = 'sa'
    update master.dbo.sysxlogins
 set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
 where name = @loginame and srvid IS NULL
if @@error <> 0
        
    raiserror(15478,-1,-1)


Wednesday, March 14, 2012 - 10:10:54 AM - TheCuriousDBA Read The Tip

"When SQL Server 2005 or above is started up in single user mode, SQL Server will automatically treat members of the local Administrators group for the operating system as members of the sysadmin fixed server role. This is true even if they don't normally have such rights. As a result, someone with administrative rights over the server can go in and manually re-add the groups/users and re-grant the permissions, thereby restoring access. "

Does this seem like a little bit of a security issue if you can get access to the local machine and/or already have rights to the local admins group and not SQL Server? 


Wednesday, March 14, 2012 - 10:29:22 AM - K. Brian Kelley Read The Tip

It is a potential security issue. This is why I tell auditors now that removing BUILTIN\Administrators should not be considered an effective control.


Wednesday, March 14, 2012 - 11:07:07 AM - Ed - sqlscripter Read The Tip

You bet, we remove that group during the build process and secure the box..


Thursday, March 29, 2012 - 2:55:08 AM - Wilfred van Dijk Read The Tip

Is access also possible with DAC?


Thursday, March 29, 2012 - 8:52:58 AM - bblack Read The Tip

Nice article ...good discussion too.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
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.