SQL Server Restricted User Mode
When performing some maintenance tasks, particularly in recovery situations, it is essential to limit access to databases by general users. In these situations, restricted access mode can be employed to limit connections to high-level users only.
ALTER DATABASE Command
The ALTER DATABASE command allows a database administrator to modify SQL Server databases and their files and filegroups. This includes changing the database user access options.
Setting a Database to Restricted User Mode
In previous articles we have seen how to set a database into a read-only mode or into a single user access mode. Each of these modes restricts access to the database by either preventing data changes or by allowing exclusive access to a database. In some situations it is necessary to restrict access to administrative users only, permitting no access to data to general users whilst allowing several high-level users to work on the database simultaneously.
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 simplest method to set a database into restricted user mode is to use the following statement:
ALTER DATABASE database-name SET RESTRICTED_USER
The above command immediately prevents new connections being made to the database by users who do not have the appropriate roles assigned. However, if such a user is already connected, she is not disconnected automatically. In this situation, the ALTER DATABASE command is blocked and waits until all such connections are closed. This ensures that no transactions are rolled back unexpectedly.
NB: Background processes that are running against SQL Server may cause the command to be indefinitely blocked, as can internal processes. The AUTO_UPDATE_STATISTICS_ASYNC in particular should be checked. If set to ON, the statement will be blocked.
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 database-name SET RESTRICTED_USER WITH NO_WAIT
Forcing Disconnection of Other Users
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 organisation, each user can be contacted and asked to disconnect. Once they have, the command will complete and restricted access will be actioned.
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 database-name SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
If an active application is accessing the database and this application can be stopped, it is useful to pause for a duration before automatically terminating any connections and rolling back transactions. This pause should be long enough for transactions to complete. This is particularly useful for web-based applications as the web site can be stopped with just enough time provided for any active or queued transactions to complete successfully. The following example statement pauses for two minutes before disconnecting users:
ALTER DATABASE database-name SET RESTRICTED_USER WITH ROLLBACK AFTER 120 SECONDS
Setting a Database to Multi-User Mode
Once the use of restricted access is no longer required, the database can be returned to multi-user mode. This is achieved with the ALTER DATABASE statement as follows:
ALTER DATABASE database-name SET MULTI_USER
4 May 2008