Database open in Restricted / Single /Multi Mode in SQL Server

Check the current user access mode for the Database:

SELECT name,user_access_desc FROM SYS.databases;

Open Database in Single User Mode:

SINGLE_USER mode only one user can access the database which has permission it can be any user.

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET  SINGLE_USER WITH NO_WAIT
GO

Open Database in Multi-User Mode( Default Mode)

MULTI_USER mode any user who has access to connect can access the database. It is the default mode.

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET  MULTI_USER WITH NO_WAIT
GO

Open Database in Restricted Mode

RESTRICTED_USER mode users who have db_owner or db_creator permission can access the database only.

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET  RESTRICTED_USER WITH NO_WAIT
GO

Note:

We can also use WITH NO_WAIT or WITH ROLLBACK option:
The ROLLBACK option: can be used to immediately disconnect users or can be provided with a number of seconds to pause before the disconnection occurs.

Change Mode from SQL Server Management Studio:

1. Right-click on Database Name and select the Properties option.
2. Go to the option in the left panel and scroll down below at last we find the option to change mode as shown: