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

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Right-click the database to change, and then click Properties.
3. In the Database Properties dialog box, click the Options page.
4. From the Restrict Access option, select Single.
5. 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.