Internal Activation - calls stored procs in other DBs

Hi all

I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".

I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?

Thanks

Ian

You are hitting the 'Extending database impersonation under EXECUTE AS context' issue. I have a series of posts in my blog tackling this problem:

http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx

The first link is posted today and is an actual example on how to call a procedure in another database under activation.

|||

Thanks for the information - it's just what I was looking for.

Ian

|||

One more question....

Is it essential that the owner of the other DB is the same as the owner of the activated stored proc?

I don't seem to be able to get this to work if they are different.

Thanks

Ian

|||You can use any user with receive permission on the queue.|||I think you must grant AUTHENTICATE permission on the 'other' DB to the user from the EXECUTE AS clause of the CREATE/ALTER procedure. If the EXECUTE AS is OWNER, then to the owner of the activated procedure)