Just a quick stupid one!
Ive set this up in its own MyAppServiceBrokerDB and it works fine for local stored procs.
How do i execute sp's in a different database? (both dbs are on the same box)
Create a local proc that is nothing more than a redirector to the one in the other db.
The Execute permission was denied on the object 'sp_name', database....
This has to do with the login associated with the connection for the service broker. You may have similar problems using the local redirector depending on your cross-database ownership chaining settings.
Ive granted execute on the destination sp to a shared user but to no avail.
This will only matter if you do it to the service broker account.
I just cant see the wood for the trees here as its not a remote call....
Actually, it is, just on the same instance. Ownership chaining and the like only work if you turn them on. You don't need the MSDTC though which is what most folks think of when dealing with remote calls. Just a matter of perspective I guess.
How are you currently doing security? Certificates? If so check out this article:
If not, you need to reference this: http://msdn.microsoft.com/en-us/library/ms166036.aspx
For a conversation that uses full security, the connection on each side of the conversation acts with the permissions of the user that is specified in the remote service binding. For example, if a remote service binding associates the service name InventoryService with the user InventoryServiceRemoteUser, SQL Server uses the security context for InventoryServiceRemoteUser to put messages for the InventoryService application on the queue for the destination service.
Under most circumstances though you'll want to work with certificates when dealing with service broker security to keep yourself sane. However, look into CREATE ROUTE (if memory serves) for determining who the owner is.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]