Workaround for TRUSTWORTHY OFF + KILL in stored proc WITH EXECUTE AS OWNER?

• Stephen.Lamb

  SSC Enthusiast

  Points: 181

  More actions

  January 16, 2015 at 6:18 pm

  #294387

  In testing SQL Server 2008 R2 – SQL Server 2014 it appears that in a database where TRUSTWORTHY is OFF, the KILL statement in a stored procedure WITH EXECUTE AS OWNER, run by a user, even one with the sysadmin role, fails with the following error.

  Msg 6102, Level 14, State 2, Line 3

  User does not have permission to use the KILL statement.

  -- Note that the real stored proc is more complicated.

  CREATE PROCEDURE [dbo].[PerformAction] WITH EXECUTE AS OWNER

  AS

  BEGIN

  -- Imagine the code grabs the SPID from a secure location.

  DECLARE @SomeSPID INT = 99;

  IF (@SomeSPID IS NOT NULL)

  BEGIN

  DECLARE @Statement NVARCHAR(MAX) = N'KILL ' + CAST(@SomeSPID AS NVARCHAR(10)) + N';';

  EXECUTE (@Statement);

  END

  END

  The KILL statement succeeds if TRUSTWORTHY is ON or if EXECUTE AS OWNER is removed and the user has the proper permissions. Is this expected behavior?

  The owner of the database is sa and sa has the server roles public and sysadmin.

  We have full control over database creation and can perform any kind of updates we like in order to address this issue.

  Would signing the stored procedure and ripping off EXECUTE AS OWNER and setting up a user that had the necessary permissions to allow the KILL statement to succeed even when TRUSTWORTHY is OFF work? I tried this but the necessary permission to execute the KILL statement, ALTER ANY CONNECTION, is a server level permission and I think is only assignable to a login. Creating a login is a bit of a security issue since customers can move databases between instances and of course we would be relying on having that login recreated when the database is restored.

  Are there other workarounds aside from the following?

  1.Don’t use KILL – this would increase complexity and cause performance problems

  2.Remove EXECUTE AS OWNER – we need non-admins to be able to do this and yes we have a bunch of checks to prevent killing sessions that shouldn’t be killed, the actual use case is very limited, we don’t just take a SPID and call KILL

  3.Set TRUSTWORTHY ON – obviously this is not good from a security standpoint

  4.Create a special login + user that has rights to execute the KILL statement and use that to connect from the application and execute the stored proc. This isn’t good from a security standpoint due to having to store connection information in the application, even if it were encrypted.