usp_KillUsers2
This script expands on the usp_KillUsers script posted by Brian Knight, by adding the text of the process that is being killed and will even kill users who are accessing the database from with in a process of another database. Does not contain the NET SEND option though.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_KillUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_KillUsers]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON
DECLARE @strSQL varchar(255)
DECLARE @handle binary(20)
PRINT 'Killing Users'
PRINT '-----------------'
create table #tmpBuffer
(
EventType nvarchar(30)
, Parameters Int
, EventInfo nvarchar(255)
)
create table #tmpLocks
(
spid smallint
, dbid smallint
, ObjId int
, IndId smallint
, Type nchar(4)
, Resource nchar(16)
, Mode nvarchar(8)
, Status nchar(6)
)
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 #tmpLocks exec sp_lock
insert into #tmpUsers exec sp_who
DECLARE LoginCursor CURSOR
READ_ONLY
FOR select distinct spid,db_name(dbid)
from #tmpLocks
where db_name(dbid) = @dbname /*SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname*/
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
DECLARE @procRunning varchar(255)
DECLARE @sqlSTMT nvarchar(2000)
DECLARE @loginname varchar(50)
DECLARE @status varchar(30)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @sqlSTMT = 'DBCC INPUTBUFFER (' + @spid + ')'
insert #tmpBuffer exec sp_executesql @sqlSTMT
select @procRunning = EventInfo
from #tmpBuffer
select @loginname = loginname,@status = status
from #tmpUsers
where spid = @spid
PRINT ' KILLING: '
PRINT ' SPID: ' + @spid
PRINT ' USER: ' + @loginname
PRINT 'STATEMENT: ' + @procRunning
PRINT ' STATUS: ' + @status
print '=========================================================================================================='
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpBuffer
DROP table #tmpUsers
DROP table #tmpLocks
