Set Database From Single User Mode to Multi User in SQL

Last Updated : 16 Dec, 2021

A single-user mode specifies that only one user at a time can access the database. If you set the database to single-user mode then all other connections will be closed without warning. If you get disconnected after setting the database into single-user mode, a different user can connect to database, but only one user.

Where as in multiple-user mode more than one user can connect to the same database at the same time. This mode is generally used in organizations so that multiple users can access it simultaneously. Areas where Multi-user database is used are Banks, supermarket, insurance agencies sector.

Here we will see, how to set database from single user mode to multi user mode.

There are two ways to perform the given activity:

Using ALTER command
Using Object explorer in SQL Server Management Studio.

For demonstration, follow the steps given below:

Step 1: Create database

Use the following command to create database.

Query:

CREATE TABLE geeks;

Step 2: Set database into single user mode

ALTER command followed by SET keyword is used to set the user mode of database. Before changing the access mode all the open connections must be closed. Use the ROLLBACK IMMEDIATE termination clause after ALTER command along with ‘WITH’ keyword to close open connection.

Syntax:

ALTER DATABASE database_name SET 
SINGLE_USER WITH ROLLBACK IMMEDIATE

Query:

ALTER DATABASE geeks SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Output:

Here, we can see that ‘single user’ is displayed alongside the geeks database name.

Method 1: Using ALTER command

We can use ALTER command in same way as we used to set the single user access mode. This commands need not connection closure.

Syntax:

ALTER DATABASE database_name 
SET MULTI_USER;

Query:

ALTER DATABASE geeks
SET MULTI_USER;

Output:

Here , ‘single user’ keyword that was displayed earlier alongside geeks database is removed now.

Method 2: Using object explorer in SQL Server Management Studio

Steps to change access mode:

Select View from menu bar.
Select Object explorer option. Object explorer will be appeared on left side of the screen.
Select Database folder and select your database (geeks we have used here).
Right click on the database and select properties option.
A dialog box will open, select ‘option’ option from left hand side of dialog box screen.
Scroll down to get ‘STATE’ field.
Select Restrict access option from STATE field.
Select MULTI_USER from dropdown menu.
Click ‘OK’.

Step 1: Open Object explorer