Drop all Connections to SQL Server Database

Here’s a simple script to drop all active connections to the SQL Server Database. You usually drop connections when you are planning to take the database offline or need to immediately deal with a maintenance issue.

If you want to view the active connections on a database, read my post View active connections for each Database in SQL Server

Drop all connections and allow database access to few users

ALTER DATABASE AdventureWorks SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

Note 1: The RESTRICTED_USER option allows the database to be accessed by only members of the db_owner, dbcreator or sysadmin roles

Note 2: The ROLLBACK IMMEDIATE option forcefully disconnects all users without allowing any work in progress to complete. If you do not plan to disconnect the users immediately, but disconnect say after 1 minute, use this option

ALTER DATABASE AdventureWorks
SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS

Drop all connections and allow database access to only one user

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The SINGLE_USER option allows the database to be accessed only by one user, who can be anyone.

Return Access to the Database as it was earlier

ALTER DATABASE AdventureWorks SET MULTI_USER