Quickly Disconnect Users From a Database

Disconnect All Users of a Database

There comes a time in every DBA’s life when he or she has many users connected to a database that needs to be detached, placed in single user mode, or simply refreshed.  The problem is they keep reconnecting more quickly than you can KILL them off.  This is especially true when you are working in an environment where the lines between testing and production are blurred.

Hopefully you know about the KILL command, and aren’t thinking I’m advocating becoming a serial killer.  Though a hatchet properly placed can cure a lot of problems, especially when you can get to the network cable!

To disconnect a user you can use sp_who2 and look at the users currently connected to the database in question.  Once you have the spid for the connection, you can issue the command:

KILL spid

The problem will be, you have to look at all the connections that are currently open, KILL those connections, then check sp_who2 again to see if any new connections were opened.  Then lather, rinse, repeat.  This process is tedious at best.

There is a better way.

The Solution

CREATE PROCEDURE kill_database_users @dbname sysname with recompile
declare @spid smallint
declare @msg    varchar(255)
declare @dbid int
       @dbid = sdb.dbid
from master..sysdatabases sdb
where   sdb.name = @dbname
declare db_users insensitive cursor for
from    master..sysprocesses sp
where   sp.dbid = @dbid
open db_users
fetch next from db_users into @spid
while @@fetch_status = 0
        select @msg = 'kill '+convert(char(5),@spid)
        print @msg
        execute (@msg)
        fetch next from db_users into @spid
close db_users
deallocate db_users

This Should close out any connections to the @dbname in question.  Once it’s complete, you can then switch to single-user mode, or detach the database, or whatever else you may need to do.  Normally, I’d avoid a cursor, but this is one of those special cases where you want to sequentially work through a list, and perform a function.  That’s strictly the domain of a cursor!


By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.


  1. The following command will disconnect all users and make sure that users cannot logon while you are making your changes?ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATEYou can allow access again with the MULTI_USER option

Leave a comment

Your email address will not be published. Required fields are marked *