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 Connection String
    ... I want to access a database which has a different owner. ... If this is SQL Server 2000, then I can guarantee you mean owner. ... Server 2005 or greater, you probably mean schema. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Truncated INSERT statements when using sp_generate_inserts by Vyas
    ... SET NOCOUNT ON ... This procedure is also useful to create a database setup, ... ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON ... IF @owner IS NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: PHP Query Issues on Ingres R3 Linux Version
    ... If a table is owned by the DBA (the database owner) then there is no ... "permission" thing so much as an object ownership issue. ... An object which is not prefixed by a schema name is found by looking ...
    (comp.databases.ingres)
  • 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)
Quantcast