Kill User Processes Per Database or Server Wide

USE [MASTER]
GO

SET NOCOUNT ON;

DECLARE @DatabaseOrServerWide CHAR(1)
DECLARE @Cursor CURSOR
DECLARE @dbid INT
DECLARE @ProcID INT
DECLARE @User VARCHAR(100)
DECLARE @DatabaseName VARCHAR(200)
DECLARE @Info VARCHAR(10)
DECLARE @ProcessDetails TABLE
(
ProcessID INT,
LoginName VARCHAR(200)
)

--Kill all server processes or just process for a specific database:
SET @DatabaseOrServerWide = 'D' -- D = Database, S = Server

--If database only, set database name:
SET @DatabaseName = 'ENTERPRISE_MARTS'


--Get process details for specified database
IF @DatabaseOrServerWide = 'D'
BEGIN
SELECT @dbid = [dbid] FROM sys.sysdatabases WHERE [name] = @DatabaseName

IF @dbid IS NULL
BEGIN
PRINT @DatabaseName + ' not found on current SQL instance.'
GOTO EndProcess
END

INSERT INTO @ProcessDetails
SELECT
MAX([spid]),
[loginame]
FROM
sys.sysprocesses
WHERE
[dbid] = @dbid
AND [loginame] != ''
AND [loginame] != 'sa'
AND [spid] != @@SPID
GROUP BY
[loginame]

IF (SELECT COUNT(0) FROM @ProcessDetails) = 0
BEGIN
PRINT 'No users currently connected to ' + @DatabaseName + ' excluding sa processes and this thread.'
GOTO EndProcess
END
END

--Get process details for entire instance
IF @DatabaseOrServerWide = 'S'
BEGIN

INSERT INTO @ProcessDetails
SELECT
MAX([spid]),
[loginame]
FROM
sys.sysprocesses
WHERE
[loginame] != ''
AND [loginame] != 'sa'
AND [spid] != @@SPID
GROUP BY
[loginame]

IF (SELECT COUNT(0) FROM @ProcessDetails) = 0
BEGIN
PRINT 'No users currently connected to instance, excluding sa processes and this thread.'
GOTO EndProcess
END
END
 

--Kill processes
SET @Cursor = CURSOR FOR 
SELECT 
ProcessID,
LoginName
FROM
@ProcessDetails

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
@ProcID,
@User

WHILE (@@FETCH_STATUS = 0)
BEGIN

EXEC('KILL ' + @ProcID)
PRINT 'Process killed from login: ' + @User

FETCH NEXT FROM @Cursor INTO
@ProcID,
@User

END

CLOSE @Cursor
DEALLOCATE @Cursor

--End information
EndProcess:

SELECT @Info = COUNT(0) FROM @ProcessDetails

PRINT ''
PRINT @Info + ' processes killed.'
PRINT ''
PRINT 'Script End'