In SQLServer 2005 one can change the user mode of a database to multi, single or restricted.
MULTI_USER – This is the default mode, means multiple user can connect to the database.
ALTER DATABASE <DB Name> SET MULTI_USER
SINGLE_USER – In single user mode only one connection is allowed at at time to the database.
ALTER DATABASE <DB Name> SET SINGLE_USER
RESTRICTED_USER – In this mode user who have dbcreator or sysadmin server role or db_owner role for that database.
ALTER DATABASE <DB Name> SET RESTRICTED_USER
To find the user mode
ALTER DATABASE AdventureWorks SET RESTRICTED_USER
GO
SELECT user_access_desc FROM sys.databases WHERE name = ‘AdventureWorks’
GO
Output:
user_access_desc
————————————————————
RESTRICTED_USER
One can specify termination mode termination mode when changing database user mode.