Different types of SQL Server Database Access mode


Different types of SQL Server Database Access mode, you can allow access to different users by setting database into below three modes.

Syntax
ALTER DATABASE DBNAME SET ACCESS MODE WITH ROLLBACK

OPTIONS
DB Access Modes
SINGLE_USER – Single user connection to the database
RESTRICTED_USER – Any number of users with db_owner or db creator or logins with sys admin rights can connect to the database
MULTI_USER - Any number of users with rights to the database can connect to the database

As you are changing the database access, you first need to take the exclusive connection database by dropping / rollback the existing connections.

You can specify below options to rollback the existing connections.

WITH ROLLBACK Options - Determines how the exclusive access to the database will take place
ROLLBACK AFTER integer [SECONDS] - Rollback the SPIDs after a particular number of seconds
ROLLBACK IMMEDIATE - Rollback the SPIDs immediately
NO_WAIT - If all of the SPIDs do not commit or rollback immediately the request to put the database in an exclusive state will fail

Examples

ALTER DATABASE DBName
SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS

ALTER DATABASE DBName
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE DBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE DBName SET MULTI_USER

About these ads

Thanks for the comment, will get back to you soon... Jugal Shah

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