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

Thank you, Dan. Both databases have the same owner the user was already in
both databases.

What else might be the problem?

"Dan Guzman" wrote:

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

.

Relevant Pages

  • Re: SQL 2005 Express-Database does not have a valid owner?
    ... That means that the database's owner is MYDOMAIN\MyUserName ... My design machine is running Windows XP Pro. ... database diagram. ... Database diagram support objects cannot be installed because this ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
    (microsoft.public.sqlserver.server)
  • Re: unknown database owner
    ... cleared for the 'Admin' user. ... the "owner is unknown" (you described ... An old database, ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.security)
  • Re: Cross database update issue
    ... Also, for dbo-owned objects, the owner of both databases must be the ... You can execute sp_helpdb to determine the current database owners. ... > User Name: Test1 Role Name=Test ... > Role Test has select permission to table t_test ...
    (microsoft.public.sqlserver.security)
  • Re: Cant access any old tables
    ... I used the same access rights to test the table in the different database. ... Is there a place to set a TABLE owner separate from the database owner? ... but one was SQL based and the other Windows Secured. ...
    (microsoft.public.sqlserver.security)