current security context is not trusted (cross db ownership chaini

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 data
bases,
and also at the instance level.
? In DatabaseB, I have compiled MySchema.MyStoredProc which selects data
from DatabaseA.
When the user executes DatabaseB.MySchema.MyStoredProc, this error is raised
:
SELECT permission denied on object 'TableA', database 'DatabaseA', schema
'dbo'.
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.
Here are the particulars:
? SQL 2005 - 9.00.1399.06 (Intel X86), Build 2600: Service Pack 2
? Both databases have Compatibility Level = SQL Server 2000, although I’
ve
changed both to 2005 and the error persists.
Also, when I look at the Database Properties (Options property page), the
‘Cross-database Ownership Chaining Enabled’ property says False, and is
disabled for editing, even though sp_configure shows the value as 1.
Thanks,
Sam Tai> When the user executes DatabaseB.MySchema.MyStoredProc, this error is
> raised:
> SELECT permission denied on object 'TableA', database 'DatabaseA', schema
> 'dbo'.
Check to ensure that both DatabaseA and DatabaseB are owned by the same
login (same authorization). Although the authorization on both the
DatabaseB.MySchema and DatabaseA.dbo schema is 'dbo', these will map to
different server principals if the database owners are different and break
the ownership chain.
Also, the user in DatabaseB will need a security context in DatabaseA, even
if no permissions are granted. You'll need to either add the user or enable
the guest user in that database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam Tai" <Sam Tai@.discussions.microsoft.com> wrote in message
news:1D3296BF-1228-4603-B433-97CC8FC53CE6@.microsoft.com...
>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 d
bo),
> and
> a ‘MySchema’ schema (also owned by dbo). I have granted select, execu
te
> on
> MySchema to MyUsers.
> ? The user in DatabaseB is in public and MyUsers database roles, and use
s
> 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.
> When the user executes DatabaseB.MySchema.MyStoredProc, this error is
> raised:
> SELECT permission denied on object 'TableA', database 'DatabaseA', schema
> 'dbo'.
> 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.
> Here are the particulars:
> ? SQL 2005 - 9.00.1399.06 (Intel X86), Build 2600: Service Pack 2
> ? Both databases have Compatibility Level = SQL Server 2000, although I
ve
> changed both to 2005 and the error persists.
> Also, when I look at the Database Properties (Options property page), the
> ‘Cross-database Ownership Chaining Enabled’ property says False, and i
s
> disabled for editing, even though sp_configure shows the value as 1.
> Thanks,
> Sam Tai|||Sam Tai (Sam Tai@.discussions.microsoft.com) writes:
> I cannot seem to get cross database ownership chaining to work.
> Heres 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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx