Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Service Broker Stored Procedure Can't Create Job Expand / Collapse
Author
Message
Posted Wednesday, May 23, 2012 4:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:23 AM
Points: 13, Visits: 120
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
Posted Thursday, May 24, 2012 2:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 1,839, Visits: 3,421
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
Posted Thursday, May 24, 2012 2:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:23 AM
Points: 13, Visits: 120
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
Posted Thursday, June 7, 2012 5:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:23 AM
Points: 13, Visits: 120
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 »

Add to briefcase

Permissions Expand / Collapse