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

By:   |   Comments (6)   |   Related: 1 | 2 | 3 | > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

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




Thursday, March 29, 2012 - 8:52:58 AM - bblack Back To Top (16700)

Nice article ...good discussion too.


Thursday, March 29, 2012 - 2:55:08 AM - Wilfred van Dijk Back To Top (16693)

Is access also possible with DAC?


Wednesday, March 14, 2012 - 11:07:07 AM - Ed - sqlscripter Back To Top (16393)

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


Wednesday, March 14, 2012 - 10:29:22 AM - K. Brian Kelley Back To Top (16390)

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 - 10:10:54 AM - TheCuriousDBA Back To Top (16387)

"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? 


Friday, March 9, 2012 - 8:45:39 AM - Ed Back To Top (16315)

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)















get free sql tips
agree to terms