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

The reason cross-database chaining works without DB_CHAINING enabled is that you turned on DB_CHAINING at the sever level with the sp_configure 'cross db ownership chaining' option. The Best Practice is to leave it off at the server level and set DB_CHAINING at the database level with ALTER DATABASE only in the specific cases where needed. Of course, you should be aware of the security implications as described in the Books Online.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sam Tai" <SamTai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1BB81952-CC26-410D-B550-1DE9E3BE13E6@xxxxxxxxxxxxxxxx
Dan,

After my first post, a colleague pointed out that I needed to use
sp_changedbowner instead of sp_configure/reconfigure. So I had already done
that. However, I must've been asleep at the wheel when I checked for the dbs
for the same owner. When I checked again, I found that they were not; and
when I made this correction, then everything started working as advertised.
So it was, as you pointed out, a problem with disparate ownership.

Thanks again for your help!
Sam Tai

"Dan Guzman" wrote:

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: 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)
  • Re: current security context is not trusted (cross db ownership ch
    ... and database level. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: Profile Trace: how to tell which database when filtering on user
    ... EXEC sp_configure 'Cross DB Ownership Chaining', ... But JOE has these privileges by virtue of membership in a db role. ... > having cross database ownership chaining enabled. ...
    (microsoft.public.sqlserver.security)
  • 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)