kill only connections of a concrete DB

Today I had the problem that in a dead-lock situation a colleague (responsible for a database on my db server) wanted to fix the situation himself. While using activity monitor he found out which process (more or less equals to connection) was responsible for the fault-situation and wanted to set up a kill statement.

For activity monitor he needed the rights ( http://msdn.microsoft.com/en-us/library/ms175518.aspx ):

• VIEW SERVER STATE
  
  and
• CREATE DATABASE, 
• ALTER ANY DATABASE, or 
• VIEW ANY DEFINITION 

Long story short he was not allowed to call kill and I was quite happy about it, because it makes sense that a data owner is not allowed to kill processes of other unrelated services. In fact to kill his own processes could also be accomplished by restarting the corresponding server application, so he seems to have some kind of power anyway. I liked to hear, that I hadn't have to get in touch with the process search, so I needed to enable process killing. My problem was how to secure the whole system against other kill requests and still offer the functionality. 

Some research later I found the concept of impersonation (execute as), but I couldn't imagine how. I tried to allow the user (who failed to kill processes) to switch its user context to a more privileged user. Nothing won, because he is still able to do what he likes. So I created a stored procedure which encapsulates this logic (-> security by obscurity... if you don't know you can switch, you wouldn't try it). Better, but not good enough, because the source of the stored procedure definition was readable, so it is easy to understand which execute as statements has to be executed to become privileged and a security issue (as good as worst case). I tried a lot using master DB, other schemas and so on and found in the end the following link.

http://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/

... with 5 golden rules which in fact really worked for me. 

1. We create a new database and set the TRUSTWORTHY flag on
2. We create a login with the permissions we want
3. Set the login as the owner of the new database
4. We create a stored procedure that does the work we want within the new database.
5. We add the EXECUTE AS OWNER clause to the SP

These were really good tips and finally it worked to impersonate the execution! I just had to hard code the database name which the executor is allowed to kill (in the following code snippet DBNAME1) and check whether the - now - reachable sysprocesses table has an entry with passed in spid and hard coded database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

if( exists (select * from master.sys.sysprocesses sproc where db_name(dbid) = 'DBNAME1' and sproc.spid = @kill_spid)) begin print 'resolved as DBNAME1 connection ... connection will be killed'; SET @KillStmt = 'kill ' + CAST(@kill_spid AS VARCHAR(50)) + '' PRINT @KillStmt EXECUTE(@KillStmt) end else begin print 'connection was not established to DBNAME1 ... request will be ignored'; end

kind regards,

Daniel