USP_KillUserProcess

CREATE PROCEDURE [dbo].[usp_KillUserProcesses] @user varchar(100)
AS

BEGIN
 
SET NOCOUNT ON

 -- variable declaration and initial values
 DECLARE @chv_killprocessestring varchar(8000)
 DECLARE @ins_initialprocesses smallint
 DECLARE @ins_remainingprocesses smallint

 SET @ins_initialprocesses = 0
 SET @ins_remainingprocesses = 0
 SET @chv_killprocessestring = ''

 -- Fetch number of processes to kill 
 SELECT  @ins_initialprocesses = COUNT(*)
 FROM master..sysprocesses 
 WHERE   loginame = @user
 AND  SPID <> @@spid

 -- Check there are processes to kill
 IF @ins_initialprocesses > 0 
 
 BEGIN
  -- Build command string of processes to kill >
  SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
  FROM   master..sysprocesses 
   WHERE   loginame = @user
  AND  SPID <> @@spid

  -- Kill processes (run command created above) >
  EXEC (@chv_killprocessestring)

  -- Fetch number of processes remaining >
  SELECT  @ins_remainingprocesses = COUNT(*)
  FROM master..sysprocesses 
   WHERE   loginame = @user
   AND  SPID <> @@spid

  IF @ins_remainingprocesses = 0
  BEGIN
  -- Report on processes killed if killed them all >
  PRINT LTRIM(RTRIM(UPPER(@user))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses)) + ' connection(s)'
  END

  IF @ins_remainingprocesses <> 0
  BEGIN
  -- Report on processes remaining if could not end them all >
  PRINT LTRIM(RTRIM(UPPER(@user))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses-@ins_remainingprocesses)) + ' connection(s), '+ RTRIM(CONVERT(VARCHAR(10), @ins_remainingprocesses)) + ' processes remaining.'
  END
 END 

IF @ins_initialprocesses = 0 
 BEGIN
  -- Report there are no processes for that user>
  PRINT @user + ' - No processes to kill'
 END
END