When working with SQL Server, there may be times when you need to disconnect all users from a database. This could be necessary when restoring a database or when you need to place the database in single user mode. In such cases, the usp_KillUsers stored procedure can come in handy.

The usp_KillUsers stored procedure is designed to disconnect all users in any given database. It works in any release of SQL Server and cycles through the list of users while issuing a KILL command for each one. This ensures that all users are disconnected quickly and efficiently.

To execute the script, simply pass it the name of the database as a parameter. For example:

EXEC usp_KillUsers 'Northwind'

Before executing the stored procedure, make sure to compile it in the Master database. Also, ensure that you are not currently connected to the database you want to disconnect users from. Otherwise, you may receive a non-fatal error.

Here’s an example of the usp_KillUsers stored procedure for SQL Server 2000:

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'

If you are using SQL Server 7.0, there is a minor adjustment to the script. In this version, the “eid” column is not a valid column for SP_WHO. Therefore, you need to use a modified version of the stored procedure. Here’s an example:

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'

It’s important to note that the provided stored procedures are a starting point and may not be suitable for all environments. There are alternative procedures available, such as the one suggested by Darwin Hatheway. His procedure avoids the use of temporary tables, which can sometimes cause blocking issues. Here’s an example of his procedure:

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

These procedures provide a way to disconnect users from a SQL Server database when needed. Whether you choose to use the usp_KillUsers stored procedure or an alternative approach, it’s important to handle user disconnections carefully to avoid any potential issues.