What type of user mode is the default in SQL Server?

What type of user mode is the default in SQL Server?

• MULTI_USER
• RESTRICTED_USER
• ROOT_USER
• SINGLE_USER 

 

EXPLANATION

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.