How to kill connections to the MSSQL database

The following script allows you to disconnect active connections to the selected ms sql database.

One should set the value of the variable @dbname to the name of the database from which we want to disconnect users.

USE [master];
DECLARE @dbname VARCHAR(256);
SET @dbname = 'tempdb';
DECLARE @t TABLE
 (SPID INT,[Status] VARCHAR(500)
 ,[Login] VARCHAR(500)
 ,HostName VARCHAR(500)
 ,BlkBy VARCHAR(500)
 ,DBName VARCHAR(500) NULL
 ,Command VARCHAR(500) NULL
 ,CPUTime INT
 ,DiskIO INT
 ,LastBatch VARCHAR(500)
 ,ProgramName VARCHAR(500) NULL
 ,SPID1 INT NULL
 ,RequestedId INT);
DECLARE @t2 TABLE ( id INT IDENTITY(1, 1) ,SPID INT );
INSERT INTO @t EXEC sp_who2;
INSERT INTO @t2 SELECT [SPID] FROM @t WHERE [DBName] = @dbname;

DECLARE @cnt INT;
DECLARE @id INT;
DECLARE @SPID VARCHAR(50);
SELECT @cnt = MAX(id) FROM @t2;
SET @id = 1;

WHILE @id <= @cnt
 BEGIN
 SELECT @SPID = CAST([SPID] AS VARCHAR(50)) FROM @t2 WHERE [id] = @id;
 EXEC('KILL ' +@SPID);
 SET @id = @id + 1;
 END

 

"Compressed" version:

 

USE [master];
DECLARE @dbname varchar(256); SET @dbname='tempdb';
DECLARE @t TABLE(SPID INT,[Status] VARCHAR(500),[Login] VARCHAR(500),HostName VARCHAR(500),BlkBy VARCHAR(500),DBName VARCHAR(500) NULL,Command VARCHAR(500) NULL,CPUTime INT, DiskIO INT,LastBatch VARCHAR(500), ProgramName VARCHAR(500) NULL,SPID1 INT NULL,RequestedId INT); DECLARE @t2 TABLE(id INT IDENTITY(1,1),SPID INT);
INSERT INTO @t EXEC sp_who2;INSERT INTO @t2 SELECT [SPID] FROM @t WHERE [DBName]=@dbname;
declare @cnt INT;declare @id INT;declare @SPID VARCHAR(50);SELECT @cnt=MAX(id) FROM @t2;SET @id=1;
WHILE @id <= @cnt BEGIN SELECT @SPID=CAST([SPID] AS VARCHAR(50)) FROM @t2 WHERE [id]=@id;EXEC('KILL ' +@SPID);SET @id = @id+1; END