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
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:
- Right-click on Database Name and select the Properties option.
- Go to the option in the left panel and scroll down below at last we find the option to change mode as shown: