Grant permission to KILL connections (sessions) for certain databases only?

Question

For a SQL Server on a VM/physical box (not Azure SQL database!)

Is this possible to grant KILL permission, but in a way that grantee can kill connections only for certain databases ?

I would not want to grant this user ability to kill any connection, but just 1 database

Thanks!!

Answer 1

Granting highly selective / fine-grained permission is rather easy via module signing:

1. USE [master]
2. Create a stored procedure to do whatever you want the low-privileged Login(s) to be able to do, with the necessary checks, etc.
3. Create a Certificate
4. Create a Login from that Certificate
5. Grant that Certificate-based Login the minimum level of permissions required to accomplish what the Stored Procedure is coded to do (in this case it might just be ALTER ANY CONNECTION (according to @sepupic)
6. Sign that Stored Procedure with that Certificate using ADD SIGNATURE
7. Grant the low-privileged Login(s) EXECUTE permission on that Stored Procedure.

HOWEVER, figuring out what Session / SPID is affecting which DB(s) is not easy. The database_id reported in sys.dm_exec_sessions is the "current" database: either what was connected to / their default DB if not specified in the connection string / whatever DB was changed to via the most recent USE statement. But the "current" database isn't necessarily where the problem is. Anyone can execute code and run queries in other DBs using 3-part names (a query can reference 3 tables, each in separate DBs, and none of them being in the "current" DB). So, I'm not sure how you would reliably enforce the "only certain DBs" constraint.

Answer 2

No. It's not possible, at least just manipulating the permissions.

To be able to kill a session one should have ALTER ANY CONNECTION server level permission. There is nothing to do with database

Answer 3

It's not pretty, but you could use a stored procedure with execute AS permissions and then grant the user in question access to that procedure. Here is something that should work to get you started.

This link https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/ goes into more detail about using EXECUTE AS in production code. Needless to say, use this with caution and make sure you really need/want to do it.

CREATE PROCEDURE usp_KillConnection (@SessionID INT = NULL)
WITH EXECUTE AS 'domain\privilegeduser'
AS

DECLARE @KillCmd NVARCHAR(4000)

IF @SessionID IS NULL
BEGIN
    --Just return all sessions for the valid database.
    SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'DatabaseName';
END
ELSE
BEGIN
    --Check to make sure the passed in session is attached to the valid database (no cheating!)
    IF EXISTS (SELECT TOP 1 1 FROM sys.sysprocesses WHERE @SessionID = spid AND DB_NAME(dbid) = 'DatabaseName')
    BEGIN
        --Show the information for that specific session.
        SELECT * FROM sys.sysprocesses WHERE @SessionID = spid AND DB_NAME(dbid) = 'DatabaseName'; 

        --Kill the session.
        SET @KillCmd = 'KILL ' + CAST(@SessionID AS NVARCHAR(5));
        EXEC sp_executesql @KillCmd;
    END
    ELSE
    BEGIN
        --throw an error if they try and kill a session that is not in the database they have access to.
        ;THROW 50000, 'Requested Session ID is not attached to a valid database', 1;
    END
END