Re: current security context is not trusted (cross db ownership ch
- From: Sam Tai <SamTai@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 10 Dec 2007 10:23:05 -0800
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
- Follow-Ups:
- Re: current security context is not trusted (cross db ownership ch
- From: Dan Guzman
- Re: current security context is not trusted (cross db ownership ch
- References:
- Prev by Date: Re: Login failed for user NT AUTHORITY\NETWORK SERVICE
- Next by Date: Re: db_owner
- Previous by thread: Re: current security context is not trusted (cross db ownership ch
- Next by thread: Re: current security context is not trusted (cross db ownership ch
- Index(es):
Relevant Pages
|
|
