Disable login & Kill SPIDS

Kill the SPIDs

Recently came across a situation where reporting logins were interfering with nightly jobs due to blocking. After a number of attempts of trying to resolve the blocking, it was decided that a stored procedure that disabled the login and killed the user sessions was the most pragmatic solution. This is the code I came up with to resolve the issue.

On the other side, I scripted out a job to re-enable that could be called from a separate stored procedure at the conclusion of the job.

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;