How to Get Exclusive Access to SQL Server Database

Introduction There are scenarios when database administrator needs to disconnect all the connected users from a SQL Server Database to get exclusive access of the Database. The exclusive access is needed before restoring a database, before a database can be detached, to perform critical maintenance tasks, to recover a database which is in Suspect Mode etc. In this article we will take a look at how database administrator can leverage ALTER DATABASE Commands to disconnect users from a database.

Using ALTER DATABASE SET Options

Database administrator can executed ALTER DATABASE command to get exclusive or restricted access of a database.

Get Restricted Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get Restrictive access to a database.

ALTER DATABASE DatabaseName
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

Get Single User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get a Single User access to a database.

ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Give Multi User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to give Multiple User access to a database.

ALTER DATABASE DatabaseName
SET MULTI_USER
GO

Difference Between SINGLE_USER, RESTRICTED_USER & MULTI_USER Alter Database SET Commands

SINGLE_USER

When SINGLE_USER WITH ROLLBACK IMMEDIATE command is used only one user can connect to the database at a time.

RESTRICTED_USER

When RESTRICTED_USER WITH ROLLBACK IMMEDIATE command is used any number of users who are in DB_OWNER, DB_CREATOR or SYSADMIN roles can connect to the database.

MULTI_USER

When MULTI_USER command is used any number of users who have rights to connect to the database will be able to connect to the database.