When performing some maintenance tasks, particularly in recovery situations, it is essential to limit access to databases by general users by restricting the user access. 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
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. But any user who is already connected is not disconnected automatically.
In this situation, the ALTER DATABASE command is blocked and waits until all such connections are closed. This ensures that notransactions are rolled back unexpectedly.
Note: The AUTO_UPDATE_STATISTICS_ASYNC in particular should be checked. If set to ON, the statement will be blocked. Background processes that are running against SQL Server may cause the command to be indefinitely blocked, as can internal processes.
Options Available which are very important:
1.Failing Immediately When Blocked: We 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
2.Forcing Disconnection of Other Users: This options 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
We can give a pause windows for transactions to complete for active transactions before we terminate them. 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 five minutes before disconnecting users:
ALTER DATABASE [Database_Name] SET RESTRICTED_USER WITH ROLLBACK AFTER 300 SECONDS
Setting a Database to Multi-User Mode: Now setting database back to multi-user mode.
This is achieved with the ALTER DATABASE statement as follows:
ALTER DATABASE [Database_Name] SET MULTI_USER