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'