none
Getting “EXECUTE permission denied on object 'sp_start_job'” from activation procedure.

    Question

  • Hi guys, please see if you can help me with this...

     

    I have an activation stored procedure that starts a SQL Agent Job which executes a SSIS package.  However when the stored procedure runs it fails with the error EXECUTE permission denied on object 'sp_start_job'. The message queue was created under the ‘sa’ account, and I have tried setting the activation procedure to run as SELF, OWNER as well as creating a user account with sysadmin rights and running it under that account, all with the same  result.  When I run the stored procedure manually (under pretty much any of the accounts I have set up) it executes without any errors and kicks off the job it is meant to.  The error only occurs when the stored procedure is activated via the service broker message queue. 

     

    I changed the stored proc to write out system_user and current_user to a table so that I could see what it was running as and as it turns out it appears to be running as the correct user (which is ‘sa’ when set to SELF) but not inheriting the correct permissions.

     

    Is this a bug, and if so is there some work-around for it?

     

     

    Thursday, January 05, 2006 7:38 AM

Answers

  • This is nothing Broker specific. Any procedure marked with EXECUTE AS would behave the same, or any batch executed after you issue an EXECUTE AS USER statement.

    The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

    A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

    Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).

    HTH,
    ~ Remus

    Thursday, January 05, 2006 7:02 PM

All replies

  • Ok so I managed to get the activation procedure to work simply by setting the Database to Trustworthy! 

    That’s good news but I’m not sure that I understand why it would be necessary to do this.  Since service broker doesn’t run as its own windows service I assume it is running as part of the database engine under the SQL Server Service.  If this is the case and since I am not using remote messaging or anything external to my SQL Server instance (the send and receive of messages happen within the same database) why would the database need to be trusted?

    Thursday, January 05, 2006 2:25 PM
  • This is nothing Broker specific. Any procedure marked with EXECUTE AS would behave the same, or any batch executed after you issue an EXECUTE AS USER statement.

    The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

    A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

    Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).

    HTH,
    ~ Remus

    Thursday, January 05, 2006 7:02 PM
  • Aah I did not realise that the trust extended only to that database.  Having now read the article it all makes sense – thanks Remus!

    Friday, January 06, 2006 9:28 AM