Database user mode

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s