In this article let us go over the following aspects of the database user access modes:

1. What are the different database user access modes and their meanings?
2. How to SET database to different user access modes?
3. How to check database current user access mode?

Let us go through the above listed topics one-by-one:

1. What are the different database user access modes for the Sql Server database and their meanings?

Following are the three possible different user access modes in Sql server:

i) SINGLE_USER Database Access Mode

In SINGLE_USER access mode at any given point of time only one user can access the database. The user can be any user who has access to the database.

Note: Before setting the database to Single user mode make sure to STOP the sql server agent or see if any AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. Otherwise the Sql Server Agent or the background thread which updates the stats may utilize the only allowed connection.

ii) RESTRICTED_USER Access Mode

In RESTRICTED_USER access mode only the users who have db_owner or db_creator permission can access. Users who belong to the sysadmin fixed server role can also access the database which is in RESTRICTED_USER access mode.

At any given point of time ZERO or Many user can access the database as long as they have specified permission as mentioned previously.

iii) MULTI_USER Access Mode

This is the default database user access mode. In this database user access mode any user who have permission to access the database can access the database.

2. How to SET database to different user access modes?

For this Demo let us first create a sample demo database with below statement:

CREATE DATABASE SqlHintsDBAccessDEMO
GO

i) How to SET database to SINGLE_USER Database Access Mode?

We can use script like below to SET database to SINGLE_USER Database Access Mode

USE SqlHintsDBAccessDEMO
GO
ALTER DATABASE SqlHintsDBAccessDEMO
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

When database is in single user mode and if already one user is connected to the database, then subsequent database connection request will fail with an error message like below:

Msg 924, Level 14, State 1, Line 1
Database ‘SqlHintsDBAccessDEMO’ is already open and can only have one user at a time.

ii) How to SET database to RESTRICTED_USER Database Access Mode?

We can use script like below to SET database to RESTRICTED_USER Database Access Mode

USE SqlHintsDBAccessDEMO
GO
ALTER DATABASE SqlHintsDBAccessDEMO
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

iii) How to SET database to MULTI_USER Database Access Mode?

We can use script like below to SET database to MULTI_USER Database Access Mode

USE SqlHintsDBAccessDEMO
GO
ALTER DATABASE SqlHintsDBAccessDEMO
SET MULTI_USER

NOTE: “WITH ROLLBACK IMMEDIATE” ALTER Database option causes all the incomplete transactions to be rolled back and any other connections to the database to be immediately disconnected.

3. How to check database current user access mode?

Below examples show how we can check the current database user access mode:

Example 1: We can use the DATABASEPROPERTYEX() function to check the database user access mode.

SELECT DATABASEPROPERTYEX('SqlHintsDBAccessDEMO','UserAccess') 
                      AS 'Current Database Access Mode'

RESULT:

Example 2: We can also use the sys.databases catalog view to check the database user access mode.

SELECT user_access_desc 
FROM SYS.databases WHERE name = 'SqlHintsDBAccessDEMO'

RESULT:

Example 3: Below examples shows how we can check whether database is in a MULTI_USER access mode.

IF(DATABASEPROPERTYEX('SqlHintsDBAccessDEMO','UserAccess') 
                         = 'MULTI_USER')
	PRINT 'Database is in MULTI_USER mode'
GO
IF(SELECT user_access_desc 
   FROM SYS.databases WHERE name = 'SqlHintsDBAccessDEMO') 
             = 'MULTI_USER'
	PRINT 'Database is in MULTI_USER mode'

RESULT:

If we want to check if database is in single user mode then we can use the scripts like below:

IF(DATABASEPROPERTYEX('SqlHintsDBAccessDEMO','UserAccess') 
                  = 'SINGLE_USER')
	PRINT 'Database is in SINGLE_USER mode'
GO
IF(SELECT user_access_desc 
   FROM SYS.databases WHERE name = 'SqlHintsDBAccessDEMO') 
               = 'SINGLE_USER'
	PRINT 'Database is in SINGLE_USER mode'

If we want to check, if database is in restricted user mode then we can use the scripts like below:

IF(DATABASEPROPERTYEX('SqlHintsDBAccessDEMO','UserAccess') 
                  = 'RESTRICTED_USER')
	PRINT 'Database is in RESTRICTED_USER mode'
GO
IF(SELECT user_access_desc 
   FROM SYS.databases WHERE name = 'SqlHintsDBAccessDEMO') 
                          = 'RESTRICTED_USER'
	PRINT 'Database is in RESTRICTED_USER mode'