What is difference between SINGLE_USER, RESTRICTED_USER and MULTI_USER?

These are the ALTER DATABASE SET commands which we can use to get exclusive access to SQL Server Database.

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.

1 2 3

ALTER DATABASE Database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO

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.

1 2 3

ALTER DATABASE Database_Name SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE GO

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.

1 2 3

ALTER DATABASE Database_Name SET MULTI_USER GO

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.

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'

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'

Ref: 

https://www.dbrnd.com/2017/05/sql-server-interview-what-is-difference-between-single_user-restricted_user-and-multi_user/

https://sqlhints.com/2014/08/30/single_user-restricted_user-and-multi_user-user-access-modes-in-sql-server/#:~:text=In%20RESTRICTED_USER%20access%20mode%20only,or%20db_creator%20permission%20can%20access.&text=At%20any%20given%20point%20of%20time%20ZERO%20or%20Many%20user,default%20database%20user%20access%20mode.