How to Start SQL Server in Single User Mode?

There are times when it is necessary to start SQL Server in Single User Mode. This can be done for various reasons such as to perform maintenance tasks or to recover a damaged or corrupt database.

In this article, we will show you how to start SQL Server in Single User Mode using different methods.

Method 1: Start SQL Server in Single User Mode from the Command Prompt

1. Open the command prompt as an administrator.
2. Type the following command and press Enter:

SQL Server 2008/2012:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>sqlservr -m;

SQL Server 2014:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr -m;

SQL Server 2016:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn>sqlservr -m;

3. Once the SQL Server has started in Single User Mode, you will be able to connect using only one user at a time. To connect, type the following command at the command prompt:

sqlcmd -S servername\\instancename -U username -P password;

Replace the servername\\instancename, username, and password placeholder values with the correct values for your environment.

Method 2: Start SQL Server in Single User Mode from the Configuration Manager

1. Open the SQL Server Configuration Manager.
2. Expand the SQL Server Network Configuration node and select Protocols for <instance name>.
3. Right-click on TCP/IP and select Properties from the context menu.
4. In the TCP/IP Properties dialog box, select the IP Addresses tab.
5. Scroll down to IPAIIntegerated Security and make sure the value is set to 1 (enabled).
6. Scroll down to IPSecurity and make sure the value is set to 0 (disabled).
7. Restart SQL Server for changes to take effect.
8. To connect to SQL Server in Single User Mode, type the following command at the command prompt:

sqlcmd -S servername\\instancename -U username -P password;

Replace the servername\\instancename, username, and password placeholder values with the correct values for your environment. Check RemoteDBA.com.

Method 3: Start SQL Server in Single User Mode from the SQLCMD utility

1. Open a Command Prompt window and type the following command:

sqlcmd -S servername\\instancename -U username -P password;

Replace the servername\\instancename, username, and password placeholder values with the correct values for your environment.

2. At the sqlcmd prompt, type the following command and press Enter:

1> sp_configure ‘show advanced options’, 1;

2> GO

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

3. At the sqlcmd prompt, type the following command and press Enter:

1> RECONFIGURE;

2> GO

4. At the sqlcmd prompt, type the following command and press Enter:

1> sp_configure ‘xp_cmdshell’, 1;

2> GO

Configuration option ‘xp_cmdshell’ changed from 0 to 1. Run the RECONFIGURE statement to install.

5. At the sqlcmd prompt, type the following command and press Enter:

1> RECONFIGURE;

2> GO

6. At the sqlcmd prompt, type the following command and press Enter:

1> xp_cmdshell ‘net start MSSQLSERVER /mSQLCMD’;

2> GO

7. Once the SQL Server has started in Single User Mode, you will be able to connect using only one user at a time. To connect, type the following command at the sqlcmd prompt:

1> connect servername\\instancename;

Replace the servername\\instancename placeholder value with the correct value for your environment.

Method 4: Start SQL Server in Single User Mode from the Management Studio

1. Open Microsoft SQL Server Management Studio.
2. Connect to the SQL Server instance that you want to start in Single User Mode.
3. In the Object Explorer pane, right-click on the server node and select Properties from the context menu.
4. In the Server Properties dialog box, select the Startup Parameters tab.
5. In the Specify a startup parameter text box, type -mSQLCMD and click Add.
6. Click OK to save changes and close the dialog box.
7. Restart SQL Server for changes to take effect.
8. Once the SQL Server has started in Single User Mode, you will be able to connect using only one user at a time. To connect, open a new query window and type the following command:

Connect servername\\instancename;

Replace the servername\\instancename placeholder value with the correct value for your environment.

Method 5: Start SQL Server in Single User Mode from the Registry

1. Open the Registry Editor.
2. Navigate to the following key:

HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL<version>.<instance name>\\SQLServer;

3. In the right pane, double-click on the LoginMode value.
4. In the Edit DWORD dialog box, type 2 in the Value data text box and click OK.
5. Close the Registry Editor and restart SQL Server for changes to take effect.

Conclusion:

There are several ways to start SQL Server in Single User Mode. The most common way is to use the Command Prompt, but it can also be done from the Management Studio or the Registry Editor. Once SQL Server is running in Single User Mode, only one user will be able to connect at a time.