Function to Forcibly Disconnect Users from a Database
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)
createprocedure sp_killusers(@database varchar(30))
as
------
-- * Created By David Wiseman, Updated 03/11/2006
-- * Version for SQL 2000
-- *
-- * 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'
------
setnocounton
-- Create temp table to store results of sp_who
createtable #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
insertinto #who execsp_who
-- Declare a cursor to select the users connected to the specified database
declare c1 cursorforselect 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 asvarchar(3))
execsp_executesql @killstatement
-- Print killed spid
print @spid
end
fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1
droptable #who
T-SQL Code (SQL 2005)
create procedure [dbo].[sp_killusers](@database varchar(30))
as
------
-- * Created By David Wiseman, Updated 19/11/2006
-- * Version for SQL 2005
-- *
-- * 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'
------
setnocounton
declare @spid int
declare @killstatement nvarchar(10)
-- Declare a cursor to select the users connected to the specified database
declare c1 cursorforselect request_session_id
fromsys.dm_tran_locks
where resource_type='DATABASE'
ANDDB_NAME(resource_database_id)= @database
open c1
fetchnextfrom 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 asvarchar(3))
execsp_executesql @killstatement
-- Print killed spid
print @spid
end
fetchnextfrom c1 into @spid
end
-- Clean up
close c1
deallocate c1