Set Database From Single User Mode to Multi User in SQL
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
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.