SQL Server SINGLE – MULTI – RESTRICTED USER

25 February,2013 by Jack Vamvas

How to set the database in SINGLE USER mode?

 

--option 1 
ALTER DATABASE db_name SET SINGLE_USER

--option 2
ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE 


 If other users are connected to the database the ALTER DATABASE command is blocked – until they finish their session or you issue a KILL command.

Set AUTO_UPDATE_STATISTICS_ASYNC  to OFF so the background task won’t take a connection  on the database , which would disallow you taking a database connection

Using the ROLLBACK IMMEDIATE termination option will rollback incomplete transactions and terminate connections

 

How to set the database in RESTRICTED USER  mode?

 

ALTER DATABASE db_name SET RESTRICTED_USER

 

RESTRICTED_USER mode limits database connections to members of the : a) db_owner fixed database role,  b) sysadmin fixed server role  and c) dbcreator.

 

How to set the database on MULTI USER mode?

 

ALTER DATABASE db_name SET  MULTI_USER

 

How to check the current status of access option?

 

--option 1
Select user_access,user_access_desc from sys.databases

--option 2 

SELECT DATABASEPROPERTYEX('db_name', 'UserAccess');

Read More on ROLLBACK and managing connections

SQL Kill connections to a database

Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database

SQL Server - Monitoring a Rollback and sys.dm_exec_requests


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server SINGLE – MULTI – RESTRICTED USER


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer