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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook