Procedure to forcibly disconnect users from a database

About

This stored procedure will take the name of a database and disconnect all the users from the database.  Any pending transactions will be rolled back.  Please use this function with caution.  The procedure creates a temporary table and stores the results of the sp_who system function in the temporary table.  A cursor is created to select the SPID column from the temporary table where the dbname column is equal to the @database parameter submitted to the function. Dynamic SQL is generated to run the KILL statement for each SPID connected to the specified database.  A check is made to ensure that the KILL statement is not run for the current user’s connection.

T-SQL Code (SQL 2000)

 

create procedure sp_killusers(@database varchar(30))

as

----------------------------------------------------

-- * Created By David Wiseman, Updated 03/11/2006

-- * http://www.wisesoft.co.uk

-- * This procedure takes the name of a database as input

-- * and uses the kill statment to disconnect them from

-- * the database.

-- * PLEASE USE WITH CAUTION!!

-- * Usage:

-- * exec sp_killusers 'databasename'

----------------------------------------------------

set nocount on

-- Create temp table to store results of sp_who

create table #who

(

spid int,

ecid int,

status varchar(30),

loginname varchar(35),

hostname varchar(15),

blk int,

dbname varchar(30),

cmd varchar(30)

)

declare @spid int

declare @killstatement nvarchar(10)

-- Run sp_who to get the users connected to each database

insert into #who exec sp_who

-- Declare a cursor to select the users connected to the specified database

declare c1 cursor for select spid from #who where dbname = @database

open c1

fetch next from c1 into @spid

-- for each spid...

while @@FETCH_STATUS = 0

begin

      -- Don't kill the connection of the user executing this statement

      IF @@SPID <> @spid

      begin

            -- Construct dynamic sql to kill spid

            set @killstatement = 'KILL ' + cast(@spid as varchar(3))

            exec sp_executesql @killstatement

            -- Print killed spid

            print @spid

      end

      fetch next from c1 into @spid

end

-- Clean up

close c1

deallocate c1

drop table #who

 

T-SQL Code (SQL 2005)

 

 

create procedure [dbo].[sp_killusers](@database varchar(30))

as

----------------------------------------------------

-- * Created By David Wiseman, Updated 19/11/2006

-- * http://www.wisesoft.co.uk

-- * This procedure takes the name of a database as input

-- * and uses the kill statment to disconnect them from

-- * the database.