SQL Server management trick: Connecting when admins are locked out

Ashish Kumar Mehta, Contributor

Securing SQL Server is one of the primary responsibilities of a database administrator. However, there are scenarios when a DBA will have to manage SQL Servers for which he doesn’t have a valid system administrator login. This can happen when an existing database administrator leaves the job without sharing the system administrator credentials; or the sysadmin account is disabled and no one knows the sysadmin password; or when all logins who had sysadmin privileges were removed accidentally.

In this tip, we will look at the steps a database administrator needs to follow to add a new user that can log in with system administrator privileges. For this demo, I will use

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in August 2012

SQL Server 2008 Express Edition. The steps mentioned in this tip are applicable across all editions of SQL Server 2005 and later versions.

How to start SQL Server in Single User Mode

  1. Click Start a All Programs a Microsoft SQL Server 2008 a Configuration Tools a SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, stop SQL Server Service by right clicking the SQL Server Service and by selecting Stop from the drop down menu.
  3. Once SQL Server Service is stopped, right click the SQL Server Service on the right side panel and choose Properties from the drop down menu.
  4. In SQL Server Properties screen, click the Advanced tab. Click and expand Startup Parameters and enter “;-m” as highlighted in the snippet below. Click OK to save the changes to SQL Server Properties.

  1. When “–m” is added as a startup parameter, the SQL Server Service is restarted. The SQL Server Database Engine will start in single-user mode. When I say SQL Server should start in single-user mode, I mean at any point in time. As long as the “–m”  startup parameter is added to the startup parameter, SQL Server will allow only one user to connect to an instance of SQL Server. For more information on different startup parameters supported in SQL Server, please refer to Database Engine Service Startup Options.
  2. Once you change the startup parameter settings, you will receive this warning message: “Any changes made will be saved; however, they will not take effect until the service is stopped and restarted.” Go ahead and restart the SQL Server Service (database engine).
  3. Once SQL Server Service is restarted successfully, SQL Server will come online in Single User Mode. Only one user connection at a time is allowed to the database engine.

It is important to remember:

  1. Never start SQL Server agent service when SQL Server is configured to run in single-user mode because the SQL server agent will occupy the available single connection.
  2. Never open SQL Server Object Explorer in SQL Server Management Studio (SSMS) or it will occupy the available single connection.
  3. Stop your Web server, or the very first application user will end up occupying the available single connection.

For more on SQL Server management

Tools to make SQL Server management simple

Password tips for more secure SQL Server management

You will receive the following error message when you are trying to connect to a SQL Server instance in single user mode and the connection is already occupied:

Login failed for user ''. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Connect to SQL Server to create a new system administrator using SQLCMD

SQLCMD is a command line utility that was introduced in SQL Server 2005 and is available in all editions of SQL Server 2005 and higher. Using SQLCMD, we will add a new login to the SQL Server instance, which will have system administrative privileges. Follow these steps to create a login with sysadmin privileges.

  1. Click Start a Run a Type CMD to open up command prompt in the command prompt type SQLCMD –E –S SERVERNAME to connect to a default instance of SQL Server. If you want to connect to a named instance of SQL Server as we're doing in this demo, type SQLCMD –E –S SERVERNAME\INSTANCENAME as shown in the snippet below. By default, SQLCMD is installed during SQL Server Installation and you can find it in the following location: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\

In SQLCMD, enter the following command to establish a connection with a SQL Server instance in single user mode:

/* In case of Default Instance */
SQLCMD –E –S SERVERNAME

/* In case of named instance */
SQLCMD –E –S SERVERNAME\INSTANCENAME

  1. In SQLCMD, to create a new user/login, enter the TSQL code below and then enter EXIT to come out of SQLCMD window as shown in the snippet below.

CREATE LOGIN DBAdmin WITH PASSWORD ='T$chT@rget'
GO
EXEC sp_addsrvrolemember DBAdmin, sysadmin
GO

  1. Once the new user is added successfully, remove the “-m;” startup parameter from the Advanced tab of SQL Server Service in SQL Server Configuration Manager, which was added earlier. Don’t forget to restart the server after removing the startup parameter to reflect the changes.
  2. Connect to SQL Server with the newly created login using SQL Server Management Stuido. Then exeute the following query to validate the SQL Server login privileges:

Use Master
GO
sp_helpsrvrolemember sysadmin
GO

In the above snippet, you'll see that the new login is created with system administrator privileges.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top