SQL Server – Kill all users script

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(50as
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