Disable a Login and Kill Its SPIDs

Kill the SPIDs

Recently ran into a situation where reporting logins were interfering with nightly jobs due to blocking. After several attempts to resolve the blocking through other means, we settled on a pragmatic solution: a stored procedure that disables the login and kills any active sessions for that user.

On the flip side, a separate job re-enables the login at the conclusion of the nightly work.

1
SET NOCOUNT ON;
2

3
DECLARE @rowsToProcess INT;
4
DECLARE @currentRow INT;
5
DECLARE @selectSpid INT;
6
DECLARE @user NVARCHAR(128);
7
DECLARE @killString VARCHAR(50);
8
DECLARE @disableLogin VARCHAR(200);
9

10
SET @user = N''; -- add login name
11

12
-- disable login to prevent new connections
13
SET @disableLogin = 'ALTER LOGIN ' + QUOTENAME(@user) + ' DISABLE;';
14
EXEC (@disableLogin);
15

40 collapsed lines
16
-- store active SPIDs for this user
17
DECLARE @spidTable TABLE (
18
    rowid       INT NOT NULL PRIMARY KEY IDENTITY(1,1),
19
    spid        INT,
20
    loginname   NVARCHAR(128),
21
    spid_status VARCHAR(15)
22
);
23

24
INSERT INTO @spidTable (spid, loginname, spid_status)
25
SELECT spid, loginame, status
26
FROM sys.sysprocesses
27
WHERE loginame = @user;
28

29
SET @rowsToProcess = @@ROWCOUNT;
30
SET @currentRow = 0;
31

32
WHILE @currentRow < @rowsToProcess
33
BEGIN
34
    SET @currentRow = @currentRow + 1;
35

36
    SELECT @selectSpid = spid
37
    FROM @spidTable
38
    WHERE rowid = @currentRow;
39

40
    SET @killString = 'KILL ' + CONVERT(VARCHAR, @selectSpid);
41

42
    -- confirm the SPID still belongs to the target user before killing
43
    IF EXISTS (
44
        SELECT 1 FROM sys.sysprocesses
45
        WHERE spid = @selectSpid AND loginame = @user
46
    )
47
    BEGIN
48
        PRINT @killString; -- verification step
49
        EXEC (@killString);
50
    END
51
    ELSE
52
        PRINT 'No spid to kill';
53

54
    WAITFOR DELAY '00:00:05'; -- optional delay between kills
55
END;

A few notes:

NVARCHAR(128) is the correct type for SQL login names — the system allows up to 128 characters and supports Unicode.
QUOTENAME() around the login name in the ALTER LOGIN statement prevents SQL injection if this gets parameterized later.
The verification check before KILL is intentional — SPIDs can disappear between when you queried them and when you try to kill them.
sys.sysprocesses is a legacy compatibility view. For newer environments, sys.dm_exec_sessions and sys.dm_exec_requests are preferred.

Key Takeaways

Disabling a login before killing sessions prevents the user from reconnecting while the cleanup runs.
Always verify a SPID still exists and belongs to the correct login before killing it — stale SPID data causes errors.
Use NVARCHAR(128) for login name storage; VARCHAR(30) will silently truncate long login names.
QUOTENAME() is non-optional when building dynamic SQL from user-supplied or variable values.