Re: current security context is not trusted (cross db ownership chaini
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 7 Dec 2007 07:40:06 -0600
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1D3296BF-1228-4603-B433-97CC8FC53CE6@xxxxxxxxxxxxxxxx
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.
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
.
- Follow-Ups:
- Prev by Date: Re: db_owner
- Next by Date: Re: Login failed for user NT AUTHORITY\NETWORK SERVICE
- Previous by thread: Re: How to genericaly search the entire db for any text string in any stored procedure, function, table definition, agent job, etc. Any one command or anyone have a stored proc that does this wide search?
- Next by thread: Re: current security context is not trusted (cross db ownership ch
- Index(es):
Relevant Pages
|
