Licence to sp_kill on SQL Server

When it comes to administering SQL Server, you sometimes need to terminate (“kill”) an active session or process. By default, only sysadmin or processadmin roles have the privilege to run the KILL command. But what if you want to empower non-sysadmin users in a very controlled way, without granting them full server-level permissions?

Meet sp_kill, a custom procedure that grants you a “license to kill” sessions under specific conditions, enforcing a minimal set of rules so that users don’t accidentally (or maliciously) kill processes beyond their scope.

1. Why Bother with a Custom sp_kill ?

Security & Granularity: You might have application owners, developers, or power users who only need to kill their own sessions or sessions in their own databases. Granting them sysadmin or processadmin is simply too broad.
Streamlined Control: SQL Server’s KILL command is powerful. A custom stored procedure lets you implement checks—like verifying if the user is the owner or a db_owner in the targeted database—before actually killing the session.
Least-Privilege Principle: You minimize the privileges assigned to users yet give them exactly what they need to do their job.

2. A Simplified Single-Procedure Approach

One approach is to store both the logic and the KILL command inside a single procedure in master, with EXECUTE AS OWNER. This means:

The procedure runs with dbo or a sysadmin account’s rights.
You can incorporate conditions to prevent undesired kills.
You grant EXECUTE on that procedure to a specific role or set of users.

Basic Flow

A user calls sp_kill @sessionId.
The procedure impersonates a privileged account.
It checks if the calling user is the owner of the session or is a db_owner in the database.
If authorized, the session is killed. If not, an error is raised.

This approach does not work because the EXECUTE AS OWNER, mandatory for KILL priviledge, will also limit the user to see others sessions than the current one. So the Single-Procedure was a dead-end.

3. The Two-Procedure Pattern

A more robust method involves two stored procedures:

sp_kill (External)
- Executes as the caller (so we can accurately check the real user’s login, roles, etc.).
- Contains all the logic (making sure they’re the same user or a db_owner in the relevant database).
- If allowed, calls the second procedure.
sp_kill_internal (Internal)
- WITH EXECUTE AS OWNER.
- Actually runs the KILL @sessionId command.
- Should not be executable directly by end users.

Why?

You want correct identity checks: SUSER_SNAME() or IS_MEMBER('db_owner') should reflect the actual calling user, not the owner.
Once verified, you escalate privileges only for the actual kill.

+---------------------+
| Non-sysadmin User   |
+----------+----------+
|
V
+---------------------------------------------------+
| sp_kill (Executes as CALLER)                      |
| 1) Checks session existence & user authorization  |
| 2) If valid, calls sp_kill_internal               |
+---------+-----------------------------------------+
|
| Authorized
V
+--------------------------------------------+
| sp_kill_internal (Executes as OWNER)       |
| 1) Possesses rights to KILL any session    |
| 2) Actually runs the KILL command          |
+--------------------------------------------+

sp_kill_internal

USE master;
GO

IF OBJECT_ID('dbo.sp_kill_internal', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_kill_internal;
GO

CREATE PROCEDURE dbo.sp_kill_internal
    @sessionId INT
WITH EXECUTE AS OWNER
AS
BEGIN
    -- The real kill command
    DECLARE @KillCommand NVARCHAR(40) = 'KILL ' + CAST(@sessionId AS VARCHAR(10));

    BEGIN TRY
        EXEC (@KillCommand);
    END TRY
    BEGIN CATCH
        RAISERROR('KILL command failed: %s', 16, 1, ERROR_MESSAGE());
    END CATCH;
END;
GO

sp_kill (the procedure that will be call by users)

USE master;
GO

IF OBJECT_ID('dbo.sp_kill', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_kill;
GO

CREATE PROCEDURE dbo.sp_kill
    @sessionId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 1) Check session existence
    IF NOT EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE session_id = @sessionId)
    BEGIN
        RAISERROR('Session %d does not exist.', 16, 1, @sessionId);
        RETURN;
    END;

    -- 2) Retrieve login & db info
    DECLARE @LoginName NVARCHAR(128), @DBName SYSNAME;

    SELECT
        @LoginName = s.login_name,
        @DBName = DB_NAME(s.database_id)
    FROM sys.dm_exec_sessions s
    WHERE s.session_id = @sessionId;

    -- 3) Check same login or db_owner
    IF @LoginName = SUSER_SNAME()
    BEGIN
        EXEC dbo.sp_kill_internal @sessionId;
        RETURN;
    END
    ELSE
    BEGIN
        DECLARE @IsDbOwner INT;
        DECLARE @sql NVARCHAR(MAX) = N'
            USE ' + QUOTENAME(@DBName) + ';
            SELECT @retVal = IS_MEMBER(''db_owner'');
        ';

        EXEC sp_executesql
            @sql,
            N'@retVal INT OUTPUT',
            @retVal = @IsDbOwner OUTPUT;

        IF @IsDbOwner = 1
        BEGIN
            EXEC dbo.sp_kill_internal @sessionId;
            RETURN;
        END
    END

    -- If neither condition matched, raise an error
    RAISERROR(
        'You do not have permission to kill session %d (db: %s, login: %s).',
        16, 1,
        @sessionId,
        @DBName,
        @LoginName
    );
END;
GO

Key points:

sp_kill doesn’t have EXECUTE AS OWNER, so user membership checks (IS_MEMBER, SUSER_SNAME) evaluate the real user’s context.
sp_kill_internal does have EXECUTE AS OWNER, which means it runs with elevated privileges to do the KILL.
Do not directly grant execute sp_kill_internal

4. Assigning Permissions

1. VIEW SERVER STATE

For a user to see all sessions via sys.dm_exec_sessions, they need VIEW SERVER STATE at the server level:

GRANT VIEW SERVER STATE TO [NonSysadminUser];

Otherwise, they’ll only see their own session.

2. Grant EXECUTE

Grant users or a custom database role the ability to run sp_kill:

GRANT EXECUTE ON dbo.sp_kill TO [YourRoleOrUser];

3. Optional: Custom Role

If you prefer, create a dedicated role in master:

CREATE ROLE session_killer_role;
GRANT EXECUTE ON dbo.sp_kill TO session_killer_role;

-- Then add members:
ALTER ROLE session_killer_role ADD MEMBER ;

5. Troubleshooting & Caveats

Permission or Identity Issues
- If the stored procedure is set to WITH EXECUTE AS OWNER but your checks rely on the original user’s identity, you’ll get incorrect results for SUSER_SNAME() or IS_MEMBER(). That’s why the two-proc approach is often better.
sys.dm_exec_sessions Visibility
- If your user is missing VIEW SERVER STATE, they’ll only see their own sessions. This might be acceptable if you only want them to kill their own connections. Otherwise, you must grant them that permission.
db_owner vs. DB Owner
- Being the literal owner of a database (i.e., the login that created it) is different from having the db_owner role membership. Adjust logic as needed.
Handling Dropped Sessions
- If a session disappears between the checks and the actual KILL, you’ll catch an error in the TRY/CATCH block.

Conclusion

By wrapping the KILL command in a custom stored procedure (or pair of procedures), you can:

Grant limited “license to kill” abilities to specific users.
Enforce checks (same user or db_owner) so that they don’t kill sessions they shouldn’t.
Keep your server security tight, following least-privilege principles.

Whether you go with a single-procedure approach (all logic + kill under EXECUTE AS OWNER) or the more robust two-procedure model (external checks under caller’s identity, then an internal kill under OWNER), you’ll be well on your way to controlling how—and by whom—SQL sessions are terminated.

Now you’ve got a “License to sp_kill”—use it wisely!

Our services