More problems with activation

I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

Activated task run under impersonated security context, similar to using the EXECUTE AS clause. Let me cross-post a reply from Remus:

<remus>
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).
</remus>

While the above could solve your problem, it may be worthwhile trying to ask yourself, why is the service located in Basket_ODS instead of Intelligence_ODS.

Hope that helps,
Rushi

|||

I actually have a sample showing how to do this: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

HTH,
~ Remus

|||

Thanks Rushi,

As usual you do a great job with follow up. The activated stored procedure copies data from the Basket_ODS database to the Intelligence_ODS database. If I move the proc to the Basket_ODS database, I ran into the same issue when it tried to copy over.

I did read the information on BOL and your earlier post on this. I guess I'll have to work my way through signing the stored procedure. The trust database option really won't be a player for me I think. I did set up trust tonight and it worked though. I have to do another run in the morning but it was incredibly slower than the copy to the same database.

I'll try to worth through the signing tomorrow. That seems the way to go for me. We are trying to operate in least-trust mode. The DBAs start getting white hair when I ask for things like database trust.

Thank you so much for your help and your contribution to the community.

Gary

|||

Thank's Remus. Wow that's a great sample. I wish I found that earlier today. Thanks again for the great information.

Gary

|||

I created the certificates as noted in your scripts. The user associated with the certificate in my system is BrokerUser2. I do have a question though.

why can't I do this?

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BrokerUser2'

)

when I try this it says BrokerUser2 does not exist or I do not have access to it. I can do an sp_helpuser 'BrokerUser2' and see his rights though.

It did appear to work if I did this:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4, EXECUTE AS OWNER)

I did create the ODS.ProcessOrderQueue stored procedure [With Execute As 'BrokerUser2'].

|||

The user executing the ALTER QUEUE statement must have IMPERSONATE permission over 'BrokerUser2'.

HTH,
~ Remus