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.

1
SET NOCOUNT ON DECLARE @rowsToProcess INT;
2
DECLARE @CurrentRow INT;
3
DECLARE @SelectSpid INT;
4
DECLARE @user VARCHAR(30);
5
DECLARE @killString varchar(50);
6
DECLARE @DisableLogin VARCHAR(100);
7

8
SET @user = ''; -- add user
9

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

14
--Create Table variable to store the spid info
15
Declare @BusinessObjectsTable Table
35 collapsed lines
16
  (
17
     rowid INT NOT NULL PRIMARY KEY IDENTITY(1,1), spid INT,
18
     loginname VARCHAR(30),
19
     spid_status VARCHAR(15)
20
  );
21

22
--query for the SPID based on user and store it in the table variable
23
INSERT INTO @ReportObjectsTable (spid, loginname, spid_status)
24
SELECT spid, loginame, status FROM sys.sysprocesses
25
        WHERE loginame = @user;
26

27
-- set rows to process
28
SET @rowsToProcess = @@ROWCOUNT;
29

30
-- loop through SPIDs to kill
31
SET @CurrentRow = 0;
32
WHILE @CurrentRow < @rowsToProcess
33
  BEGIN
34
    SET @CurrentRow = @CurrentRow + 1;
35
    SELECT @SelectSpid = spid FROM     @ReportObjectsTable
36
       WHERE rowid = @CurrentRow
37

38
    SET @killString = 'Kill ' + CONVERT(VARCHAR, @SelectSpid);
39

40
    -- verify spid still exits and belongs to correct user before killing
41
    IF EXISTS(SELECT spid, loginame FROM sys.sysprocesses
42
        WHERE spid = @SelectSpid AND loginame = @user)
43
    BEGIN
44
     PRINT @killString; -- verificatioin step I used to test against
45
     EXEC (@killString)
46
    END;
47
    ELSE
48
     PRINT 'No spid to kill'
49
     WAITFOR DELAY '00:00:05'; -- add delay if needed
50
END;

Disable login & Kill SPIDS

Kill the SPIDs