SQL Server – Grant Permission to KILL Connections for Certain Databases

killpermissionssql server

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

Best Answer

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.

Related Question