The usp_KillUsers stored procedure will disconnect all users in any given database. The script works in any release of SQL Server and cycles through the list of users while issuing a KILL command for each one. This is handy when you want to restore a database and can't disconnect the users quick enough. You can also use it in 7.0 before placing the database in single user mode. To execute the script, simply pass it the database name as shown below:

usp_KillUsers 'Northwind'

Make sure you compile the stored procedure in the Master database and make sure you're out of the database yourself before issuing the command. Otherwise, you'll receive this non-fatal error:

Server: Msg 6104, Level 16, State 1, Line 1 Cannot use KILL to kill your own process.

Without further buildup, here's the script for SQL Server 2000 (keep reading if you're a 7.0 user):

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON
DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'
CREATE table #tmpUsers(
 spid int,
 eid int,
 status varchar(30),
 loginname varchar(50),
 hostname varchar(50),
 blk int,
 dbname varchar(50),
 cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        PRINT 'Killing ' + @spid
        SET @strSQL = 'KILL ' + @spid
        EXEC (@strSQL)
        END
        FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go

With SQL Server 7.0, there is a minor adjustment. This is because the eid is not a valid column for SP_WHO in SQL Server 7.0. If you have SQL Server 7.0, run the following script:

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON
DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'
CREATE table #tmpUsers(
 spid int,
 status varchar(30),
 loginname varchar(50),
 hostname varchar(50),
 blk int,
 dbname varchar(50),
 cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        PRINT 'Killing ' + @spid
        SET @strSQL = 'KILL ' + @spid
        EXEC (@strSQL)
        END
        FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go

Feedback

This article has received quiet a bit of emails over the past few days. One I particularly liked was from Darwin Hatheway, who had an alternative procedure. The procedure I provided above was one to get you started and may not be suitable for some environments. Darwin's alternative procedure was a fantastic one through and deserves posting. Here's his email:

I avoid temp tables when I can. If someone has a catalog-level lock in TEMPDB, your temp table create blocks you until the TEMPDB locks clear. This is the procedure I use:

CREATE PROCEDURE kill_database_users @arg_dbname sysname with recompile
AS
-- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000
declare @a_spid smallint
declare @msg    varchar(255)
declare @a_dbid int
select
        @a_dbid = sdb.dbid
from    master..sysdatabases sdb
where   sdb.name = @arg_dbname
declare db_users insensitive cursor for
select
        sp.spid
from    master..sysprocesses sp
where   sp.dbid = @a_dbid
open db_users
fetch next from db_users into @a_spid
while @@fetch_status = 0
        begin
        select @msg = 'kill '+convert(char(5),@a_spid)
        print @msg
        execute (@msg)
        fetch next from db_users into @a_spid
        end
close db_users
deallocate db_users
GO