management->current activities->Process Info->kill thread if u are admin
CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as SET NOCOUNT ONDECLARE @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 = @dbnameDECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursorFETCH 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 ENDCLOSE LoginCursor DEALLOCATE LoginCursorDROP table #tmpUsers go
SET NOCOUNT ONDECLARE @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 = @dbnameDECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursorFETCH 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
ENDCLOSE LoginCursor
DEALLOCATE LoginCursorDROP table #tmpUsers
go