Re: current security context is not trusted (cross db ownership chaini

Sam Tai (Sam Tai@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I cannot seem to get cross database ownership chaining to work.

Here’s what I have so far:
• I have a user in DatabaseA who is only in the public database role.
• In DatabaseB, I have created a ‘MyUsers’ database role (owned by dbo),
and a ‘MySchema’ schema (also owned by dbo). I have granted select,
execute on MySchema to MyUsers .
• The user in DatabaseB is in public and MyUsers database roles, and uses
MySchema as the default schema.
• I have enabled the ‘cross db ownership chaining’ option in both
databases, and also at the instance level.
• In DatabaseB, I have compiled MySchema.MyStoredProc which selects data
from DatabaseA.

You seem to be missing one thing: you need to do

ALTER DATABASE db SET DB_CHAINING ON

in both databases.

When MyStoredProc is recompiled WITH EXECUTE AS SELF (or OWNER), this
error is raised: Access to the remote server is denied because the
current security context is not trusted.

This is because EXECUTE AS sets a database-user context, and you are
sandboxed into the current database. You make this, you need to set
the database as trustworthy. You also need to deal with certificates.
I have this in more detail on my web site in this article:
http://www.sommarskog.se/grantperm.html


Here are the particulars:
• SQL 2005 - 9.00.1399.06 (Intel X86), Build 2600: Service Pack 2

Oh-oh, that is SQL 2005 RTM. I would recommend that you install SP2. To
make things a little more complicated there are some seroius bugs with
maintenance plans in SP2 as it was released, so make sure that you have
at least version 9.00.3054 when you are done. (The "Service Pack 2" in
the string relates to Windows.)

I should emphasize that this is not related to your issue, just a general
piece of advice.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages