Set Database From Single User Mode to Multi User in SQL

Last Updated : 16 May, 2023

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.

Why Set Database From Single User Mode to Multi User in SQL ?

Setting a database from single-user mode to multi-user mode in SQL Server is important for several reasons:

Enables concurrent access: Single-user mode restricts the database to one user at a time, which can be problematic in a multi-user environment. By setting the database to multi-user mode, multiple users can access the database concurrently, improving productivity and efficiency.
Facilitates collaboration: Multi-user access to a database facilitates collaboration between users, enabling them to work on the same data simultaneously. This is especially useful for teams working on a shared project or application.
Prevents downtime: If a database is stuck in single-user mode, other users are unable to access it, resulting in downtime for the application or system that depends on it. Setting the database to multi-user mode resolves this issue and helps ensure that the application or system remains available to users.
Eases maintenance tasks: Single-user mode is often used for performing maintenance tasks or making significant changes to the database schema or data. However, once these tasks are completed, it is necessary to switch back to multi-user mode to allow other users to access the database. This facilitates the maintenance of the database and ensures that it is available for use by other users when maintenance tasks are complete.

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