By David Wiseman (Administrator)published 23 Dec 2006, modified 02 Mar 2009
My Rating:
Vote
Rating:
Not Rated
Views:13772

T-SQL sp_KillUsers Stored Procedure
 A T-SQL Stored 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.
-- * PLEASE USE WITH CAUTION!!
-- * Usage:
-- * exec sp_killusers 'databasename'
----------------------------------------------------
set nocount on
declare @spid int
declare @killstatement nvarchar(10)
 
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
                                from sys.dm_tran_locks
                                    where resource_type='DATABASE'
                                    AND DB_NAME(resource_database_id) = @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
 

 




  Post Comment
Order By:  
User Comments
      
Be the first to post a comment!