SET NOCOUNT ON 
DECLARE @rowsToProcess INT;
DECLARE @CurrentRow INT; 
DECLARE @SelectSpid INT;
DECLARE @user VARCHAR(30); 
DECLARE @killString varchar(50); 
DECLARE @DisableLogin VARCHAR(100); 

SET @user = ''; -- add user 

-- disable login of user to prevent more logins 
SET @DisableLogin = 'Alter Login ' + @user + ' Disable;'; 
EXEC(@DisableLogin); 

-- Create Table variable to store the spid info 
Declare @BusinessObjectsTable Table
 ( 
 	rowid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	spid INT, 
	loginname VARCHAR(30), 
	spid_status VARCHAR(15)
 ); 
 
-- query for the SPID based on user and store it in the table variable 
INSERT INTO @ReportObjectsTable (spid, loginname, spid_status) 
SELECT spid, loginame, status FROM sys.sysprocesses 
	WHERE loginame = @user; 
	
-- set rows to process 
SET @rowsToProcess = @@ROWCOUNT; 

-- loop through SPIDs to kill 
SET @CurrentRow = 0; 
WHILE @CurrentRow < @rowsToProcess
 BEGIN 
 	SET @CurrentRow = @CurrentRow + 1; 
	SELECT @SelectSpid = spid FROM @ReportObjectsTable 
		WHERE rowid = @CurrentRow 
			
	SET @killString = 'Kill ' + CONVERT(VARCHAR, @SelectSpid); 
	
	-- verify spid still exits and belongs to correct user before killing 
	IF EXISTS(SELECT spid, loginame FROM sys.sysprocesses 
		WHERE spid = @SelectSpid AND loginame = @user) 
		
	BEGIN  
		PRINT @killString; -- verificatioin step I used to test against 
		EXEC (@killString) 
	END; 
	ELSE 
		PRINT 'No spid to kill'  
		WAITFOR DELAY '00:00:05'; -- add delay if needed 
END;