kill – Codebureau – Matt Simner

Found this the other day – for when you’re restoring SQL databases. You need to kick out all current users right?

Run this little fella into your master database and away you go – just run this step before your restore.

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON

DECLARE @strSQL varchar(255)
PRINT ‘Killing Users’
PRINT ‘—————–‘

CREATE table #tmpUsers(
spid int,
dbname varchar(50))

INSERT INTO #tmpUsers
SELECT
[Process ID]    = l.req_spid, db_name(l.rsc_dbid)
from master.dbo.syslockinfo l with (NOLOCK)
where db_name(l.rsc_dbid) = @dbname
group by l.req_spid, db_name(l.rsc_dbid)

DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH 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
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers

Colorized by: CarlosAg.CodeColorizer