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 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
- Click Start a All Programs a Microsoft SQL Server 2008 a Configuration Tools a SQL Server Configuration Manager.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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:
- 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.
- Never open SQL Server Object Explorer in SQL Server Management Studio (SSMS) or it will occupy the available single connection.
- Stop your Web server, or the very first application user will end up occupying the available single connection.
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.
- 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
- 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'
EXEC sp_addsrvrolemember DBAdmin, sysadmin
- 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.
- 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:
In the above snippet, you'll see that the new login is created with system administrator privileges.