Open In App
Related Articles

Set Database From Single User Mode to Multi User in SQL

Improve Article
Save Article
Like Article

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.



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.


ALTER DATABASE database_name SET 




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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.


ALTER DATABASE database_name 




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

Step 2: Select properties

Step 3: Select option keyword from dialog box

Step 4: Select MULTI_USER from dropdown list in Restrict Access field

Step 5: Click on Ok, A dialog box will appear. Now click on Yes button.

Step 6: Again open object explorer

We can see that the ‘single user’ keyword is no longer present with the database name.

Last Updated : 16 May, 2023
Like Article
Save Article
Similar Reads
Related Tutorials