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

I think Erland identified the issue is that DB_CHAINING is not turned on. DB_CHAINING, 'cross db ownership chaining' and '‘Cross-database Ownership Chaining' are sometimes used interchangabily. Although you mentioned that 'cross db ownership chaining' was turned on in both databases, I see at the end of you initial post that the ‘Cross-database Ownership Chaining Enabled’ property shows 'False' in both databases. That property is actually the DB_CHAINING that you can set with ALTER DATABASE.

Below is a test script to illustrate:

CREATE LOGIN DatabaseBUser WITH PASSWORD='secretpassword'
GO

CREATE DATABASE DatabaseA
GO
USE DatabaseA
EXEC sp_changedbowner 'sa'
CREATE USER DatabaseBUser
CREATE TABLE dbo.TableA(Col1 int)
GO

CREATE Database DatabaseB
GO
USE DatabaseB
EXEC sp_changedbowner 'sa'
CREATE USER DatabaseBUser
CREATE ROLE MyUsers AUTHORIZATION dbo
GO
CREATE SCHEMA MySchema AUTHORIZATION dbo
GO
GRANT SELECT, EXECUTE ON SCHEMA::MySchema TO MyUsers
EXEC sp_addrolemember 'MyUsers', 'DatabaseBUser'
GO
CREATE PROC MySchema.MyStoredProc
AS
SELECT Col1 FROM DatabaseA.dbo.TableA
GO

EXECUTE AS LOGIN = 'DatabaseBUser'
GO
EXECUTE MySchema.MyStoredProc --fails with SELECT permission error
GO
REVERT
GO

ALTER DATABASE DatabaseA SET DB_CHAINING ON
ALTER DATABASE DatabaseB SET DB_CHAINING ON
GO

EXECUTE AS LOGIN = 'DatabaseBUser'
GO
EXECUTE MySchema.MyStoredProc
GO
REVERT
GO



--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sam Tai" <SamTai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:52F2DE58-9F94-481B-AE7B-94FF08BAB640@xxxxxxxxxxxxxxxx
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: Need help on how to organize users and objects
    ... create and execute procedures and such ... tables they need and I grant them select permissions to them. ... so I think that the best way will be to create Schema to Role and then you ... and then create an appropriate permissons for those roles in database ...
    (microsoft.public.sqlserver.security)
  • SQL Adapter -Stored Procedure Permissions- issue, -SQL 2005-
    ... Our SQL security requires that Users have permission to execute stored ... 'Security Users' of the database, then we add that user to a 'Roles ... SQL Adapter schema generation wizard and when the schema is created ...
    (microsoft.public.biztalk.general)
  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... The server principal "HFDB_2_0_readonly" is not able to access the database "Hotfix_data" under the current security context. ... To add on to Erland's response, the user needs a security context in both databases, even if no permissions are granted. ... I'm trying to set up Ownership Chaining. ... When I set up both a table and view from one logon and granted permissions to the view it didn't work, evidently because "creating user" is not equivalent to OWNER. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... ALTER DATABASE only in the specific cases where needed. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... The instance of SQL Server will recognize this setting when the cross ... database option is ignored when 'cross db ownership chaining' of sp_configure is set to 1. ...
    (microsoft.public.sqlserver.security)
Quantcast