SQL Server user mode

SQL Server user mode can be defined as

 

1.    Multi user mode

2.    Restricted user mode

3.    Single user mode

 

This can be done by simple ALTER DATABSE statements.

 

In this article I am explaining related to 2 and 3.

 

RESTRICT USER MODE

 

In case of Restricted User Mode, when we are going to run maintenance tasks or taking the backup/restore, then we prefer this mode.

 

SQL Server's restricted access option provides a special access mode that permits multiple connections by users of specific groups. These are users with either of the "sysadmin" or "dbcreator" server roles, or users with the "db_owner" role for the database being modified. Any other users that attempt to connect to the database receive an error.

 

The simple method is to use this is:

 

ALTER DATABASE db-name SET RESTRICTED_USER

 

 

Failing Immediately When Blocked

 

If you run the ALTER DATABASE command when it is impossible to switch to restricted access mode, but you do not wish the command to be blocked, you can execute the statement with the NO_WAIT option. This causes the command to fail if the database mode cannot be changed immediately.

 

ALTER DATABASE db_name SET RESTRICTED_USER WITH NO_WAIT

 

Forcing Disconnection

 

In some cases it is appropriate to run the ALTER DATABASE command and allow it to be blocked until all of the non-qualifying users and processes disconnect. For SQL Servers that are used only internally to an organization, each user can be contacted and asked to disconnect. Once they have, the command will complete and restricted access will be auctioned.

In other situations, it may not be possible to request that general users disconnect. In these cases it can be appropriate to automatically disconnect users and to roll back their active transactions. This can be achieved using the ROLLBACK termination clause.

The ROLLBACK clause can be used to immediately disconnect users or can be provided with a number of seconds to pause before the disconnection occurs. To force the immediate disconnection of general users and processes and the rolling back of their transactions, use the following command:

 

 

ALTER DATABASE db_name SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

 

SINGLE USER MODE

 

When the data based is used by single user only then this mode is specified. All other users want to connect to the data base get Error.

 

ALTER DATABASE db-name SET SINGLE_USER

 

 

 

Posted by: MR. JOYDEEP DAS