SQL 2000+

SQL Server Restricted User Mode

by Richard Carr, published at http://www.blackwasp.co.uk/SQLRestrictedUser.aspx

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:

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.

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:

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:

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: