Personal tools
You are here: Home SQL Server How To's How To Kill All Users In Specific Database On SQL Server
Navigation
Log in


Forgot your password?
 
Document Actions

How To Kill All Users In Specific Database On SQL Server

This tip describe how to kill all the users in specific database on SQL Server. Is very common to need to kill all the users to perform maintenance tasks like reindex, change some properties and so on.

-- First you can try the sysprocesses to get SPID and Kill Them
DECLARE @DB SYSNAME -- Put the name of database here
DECLARE @SPID VARCHAR(4), @cmdSQL VARCHAR(10)

SET @DB = 'Northwind'

-- Looks for all spids that are connected in the databases
DECLARE cCursor CURSOR
FOR SELECT CAST(SPID AS VARCHAR(4)) FROM master.dbo.sysprocesses
WHERE SPID > 50 -- To not try to Kill SQL Server Process
AND SPID != @@SPID -- To not kill yourself
AND DBID = DB_ID(@DB) -- To filter only the users connected in specific database

OPEN cCursor

FETCH NEXT FROM cCursor INTO @SPID

-- For each user connected in the database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmdSQL = 'KILL ' + @SPID

-- Kill the user
EXEC (@cmdSQL)
FETCH NEXT FROM cCursor INTO @SPID
END

CLOSE cCURSOR

DEALLOCATE cCURSOR



Another (and easy) Option is put the database in offline mode (rollback users)


ALTER DATABASE <Put Your Database Here> SET OFFLINE WITH ROLLBACK IMMEDIATE

-- And to bring the database online again
ALTER DATABASE <Put Your Database Here> SET ONLINE



Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls