How To: Set a Database to Single-User Mode
The database often needs to be set to a single-user mode for database maintenance jobs. This article describes how to set a user-defined database to single-user mode in SQL Server 2016 by using SQL Server Management Studio.
Step-by-step guide
Before getting in the steps of how to enable the single-user mode; check out the following details -
Limitations and Restrictions
If other users are connected to the database at the time that the database is set to single-user mode, their connections to the database will be closed without warning.
The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.
Prerequisites
Before the database is set to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and the database cannot be accessed in a single-user mode.
Security
Permissions - Requires ALTER permission on the database.
To set a database to single-user mode
- In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
- Right-click the database to change, and then click Properties.
- In the Database Properties dialog box, click the Options page.
- From the Restrict Access option, select Single.
- If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.