3
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!!

  • Do end users ever run queries that references tables from multiple databases? – Joe Obbish Sep 13 '17 at 13:42
5

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.

| improve this answer | |
3

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

| improve this answer | |
0

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
| improve this answer | |
  • 2
    In this code 'domain\privilegeduser' is a user and not a login. To make it work you need to make your db TRUSTWORTHY. I think it should be mentioned in the answer, as it can open a secutirty hole on the server – sepupic Sep 13 '17 at 13:29
  • @sepupic is correct that this permission is server-level, so TRUSTWORTHY ON is needed for the domain\privilegeduser User to link to its associated Login. And that TRUSTWORTHY ON is undesirable. Impersonation / EXECUTE AS is almost, if not entirely, never needed as module signing fixes almost everything, if not everything. Please see my answer for details. Also, I wouldn't use sys.sysprocesses as it's a compatibility view. Instead use some combination of sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections. – Solomon Rutzky Sep 13 '17 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.