Single User , Restricted User and Multi_user database mode in SQL Server 2008/2005

We may need to put our database in single_user mode and restricted_user mode sometime. I have observed that many of the developers are not aware with the differences between these two modes. It becomes very sensitive to choose right mode in certain situation. Let us see what it is.

Single_User Mode:

-- does not keep database open only for DBO, first come and first served basis.

-- Database can only be accessed by one user and that user can be any one.

Restricted_User Mode:

-- restrict database for DBO user, any user can login to database as long as they have permission of db_owner, db_creator and sysadmin

Multi_User Mode:

-- Default mode of database, this is normal behavior and you should put your database to Multi_User mode as long as you finish your task with Single_User/Restricted_User.

Let us see small code snippet about how we can set database to different modes.

use master

go

ALTER DATABASE adventureworks

SET single_user WITH ROLLBACK IMMEDIATE;

go

ALTER DATABASE adventureworks

SET restricted_user WITH ROLLBACK IMMEDIATE;

go

ALTER DATABASE adventureworks

SET multi_user WITH ROLLBACK IMMEDIATE;

go

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com