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)

Inter Database Security

I have a stored procedure db1.dbo.sp1
this Stored Procedure grabs data from another database. Say db2.dbo.tbl1

If i call the qry1.
how does the security from db2 come in to play? if at all?

For example, If I am SQL Server Authenticated, for DB1 but not for DB2 will the SP fail. etc.If you have security to db1 but not db2, and you run it as yourself, it will fail.

HTH|||Tnx for the info. I picked up some additional tips and was able to confirm your suggestion on our sql servers over here.

Is there anyway to use NT Authentication in the first db. Then from within a stored procedure (in the 1st database), access the 2nd database using the sa account?

I.E. pass a sa login request to the 2nd database.

Integration Help

I have a Visual Studio 2005 professional and Sql Server 2005 Express.

I download the 'Books on line for Sql Server' but when call help in VB interface or in

Management Sql Express the message appear:

Integrated help is not available ....

This is a bug?

Thanks,

Marsenne

Hi Marsenne,

It would be helpful to know the following information:

1. What version of Books Online did you download? The December 2005 or April 2006 version?

2. In what order did you install the components (VS, Express, Books Online).

3. Did you install Express from the VS installation or did you download SQL Express separately?

Regards,

Gail

|||

Hi Gail,

1. The version is December 2005 (SqlServer2K5_BOL_Dec2005.msi)

2. I install in order: VS, Express, Books Online.

3. Begin, I install Sql Express include in VS, but do not language brazilian portuguese. I remove this and install Sql Express spk1 (download).

Thanks and Regards,

Marsenne Negreiros

|||

Hi Marsenne,

It may be some issue between the December BOL and SQL Express SP1. WIll you please download the April version of BOL from here: (http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en and see if you have the same problem?

Thanks,

Gail

|||

Hi Gail,

I unistall the Dez/2005 and Install the Abri/2006.

The help is now integrated. Very good.

Thanks,

Marsenne

|||

Hi Marsenne,

Glad to hear that solution worked for you.

Regards,

Gail