December 5, 2011 at 6:22 am
Ive been asked to customise a COTS product and dont want to turn on service broker on that db but i do have some bespoke stored procs that ive created to batch load some data.
The main batch of this loading is an SSIS package and at the end i'd like to write the request to a queue to kick off some other processing (which is handled by teh service broker) whilst the ssis package continues to load.
Ive set a test example in its own MyAppServiceBrokerDB and it works fine for local stored procs. I can write to the queue and it executes a simple count proc.
Now, i want to execute my bespoke sp in the COTS db
How do i execute sp's in a different database? (both dbs are on the same box)
I keep getting
The Execute permission was denied on the object 'sp_name', database....
Ive granted execute on the destination sp to a shared user but to no avail.
I just cant see the wood for the trees here as its not a remote call....
Any help is greatly appreciated.
December 5, 2011 at 6:58 am
Which account are you using to run the package? Does it have permissions on both dbs?
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
December 5, 2011 at 7:30 am
Thanks for your reply.
Its not the package isnt an issue as i can run the sp from ssms to add the request to the service broker queue.
Its from there thats the issue. So is it the Service Broker security context?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply