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
- Practice this technique on a development or test SQL Server (not production) that isn’t being used so you become comfortable with how to get back in should someone remove the SQL Server access incorrectly.
- Audit your SQL Servers to know who is in the local Administrators group and can therefore use this method to get in.
- Learn how to connect to SQL Server using the SQLCMD utility.
