In this blog, we are showing how to setup Single User Mode in Microsoft SQL server.

Prerequisite-
1. Microsoft SQL server configured with mixed Authentication (Windows and SQL).
2. User should be at least local administrator of server.

Solutions-
1.  Open the SQL Server Configuration manager.

2. Go to SQL Server Services and Stop the SQL Server Instance.

3.  Right click on SQL Server Instance and select the Properties.

4. Go to Startup Parameters tab, type –m to specify the startup parameter and click on Add button.
5. Click on Apply and then OK.

Note– If SQL server is before 2014 we could not find the Startup parameter tab but can change the start-up parameter by going to Advanced and looking for the Startup parameter.

6. Click OK on Warning which prompts after apply the changes in Startup Parameter.

7.  Now, Start SQL Server Instance.

 

8. Open the SQL command prompt or Studio to run the SQL in Single user mode.

To return to the normal mode remove the Startup Parameters –m

1. Go to SQL Server Services and Stop the SQL Server Instance.
2. Now go to SQL Server Instance clicks on Properties.

3. Go to Startup Parameters tab and remove startup parameter –m from Existing parameters after that click on Apply then OK.

4.   Now start the SQL instance again and start using MS SQL DBs.