Service Broker Stored Procedure Can't Create Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » Service Broker » Service Broker Stored Procedure Can't Create...

Service Broker Stored Procedure Can't Create Job

Rate Topic

Display Mode

Topic Options

Author

Message

Stuart Pearson

Posted Wednesday, May 23, 2012 4:44 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:59 PM
Points: 11, Visits: 102

I have a service broker queue that is serviced by a stored procedure which calls another stored procedure to run an SSIS package.
This second procedure runs the SSIS package by creating a Job and running it (see Calling a SSIS Package from a Stored Procedure).

The problem I'm having is that when the queue is serviced I get an error saying that the user does not have execute permissions for the sp_add_job procedure.

The user running the process is in the SQLAgentOperator role in msdb

When running the second stored procedure directly (the one that creates the job) it works perfectly.
It's just when that procedure is run from the service broker procedure that it fails.

The code to set up the procedure to service the queue is

ALTER QUEUE ProcessUploadQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = Upload,
      MAX_QUEUE_READERS = 1,
      EXECUTE AS 'SPUser'
    )
GO

Does anyone have any ideas what I might be doing wrong.

Thanks

Stuart

Post #1305435

Nils Gustav Stråbø

Posted Thursday, May 24, 2012 2:07 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:44 PM
Points: 1,786, Visits: 3,323

Is your service broker target queue in msdb? The user running the activation procedure will not be impersonated across databases, so if your service broker objects are in Database1, then the activation procedure cannot execute procedures in other databases (like msdb).

I found an article explaining this a little bit better :

http://www.databasejournal.com/features/mssql/article.php/3800181/Security-Context-of-Service-Broker-Internal-Activation.htm

Post #1305578

Stuart Pearson

Posted Thursday, May 24, 2012 2:52 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:59 PM
Points: 11, Visits: 102

Thanks Nils
The Service broker artefacts are NOT in the msdb they're in my database.

I've read the article you linked to and if I understand it right then setting my database to trustworthy and granting my user authentication rights to msdb should resolve the problem.

Unfortunately after doing this it is still not working.

Am I still missing something?

Stuart

Post #1305615

Stuart Pearson

Posted Thursday, June 07, 2012 5:18 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:59 PM
Points: 11, Visits: 102

Sorted.
Needed to set the stored proc that serviced the queue to run as owner and made sure that owners was in the SQL Agent USer role in msdb and also had rights to run the stored procedure in msdb
Also had to digitally sign the stored procedure in my source database and use the certificate to create a user in msdb

Post #1312394

« Prev Topic | Next Topic »

Permissions