How to set database in single user mode on SQL Server

In this article, we will see how to set database in single user mode on SQL Server by using GUI method and SQL Statement.

Why single user mode?
1. Database Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance tasks.

2. If other users are connected to the database at the time that we want set the database to single-user mode, their connections to the database will be closed without warning.

Permissions Required to set single user for database in SQL Server:

To set single user mode, ALTER DATABASE permissions required.

1. Set database in single user mode on SQL Server using SSMS:

1. Right-click the database to change, and then click Properties as shown below.

2. In the Database Properties dialog box, click the Options page.Then the Restrict Access option, select SINGLE_USER as shown below.

3. Then, click on Yes for confirmation.

4. Now, observe that ‘Single User’ is added to the database in the databases.

2. Set database in single user mode on SQL Server using SQ Statement:

In the following example we are going to set single user for the database ‘training’.

USE master;
GO
ALTER DATABASE training
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
--Set multi user mode
ALTER DATABASE training
SET MULTI_USER;
GO