Cross-DB Ownership Chaining problem
-
We want to setup a gateway db to host stored procedures which use tables from other databases ( which are in read_only mode )
This should be very simple to setup, as long as the database owner accounts are the same.
Well .. Apparently not.
I've user the attached setup script and it ended up with this error:
Currently I'm just walking circles, pondering what's wrong.
Does anybody use xDB owership chaining at all ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
-
Never give up ...
So, for Cross-DB ownership chaining to work, it is mandatory all involved schemas and objects have the same owner !
If not, the chain breaks, and it all fails.
ownership mismatch between the procedure and the target tables causes problems !.
Ownership chaining only works when every object in the chain shares the same owner.
SQL Server checks ownership at each "link."
If the procedure in GatewayDB is owned by dbo (NonSA in my case) but the table in the target database is owned by a different principal — or vice versa — the chain breaks
and SQL Server falls back to checking the caller's explicit permissions, which AppReaderLogin doesn't have.
The fix?
To fix the schema ownership, use this script:
SELECT '
Declare @Auth Nvarchar(max) = ''''
Select @Auth = @Auth + ''ALTER AUTHORIZATION ON SCHEMA::[''+name+''] TO dbo; ''
from sys.schemas
where principal_id <> 1
and name not in (''guest'',''INFORMATION_SCHEMA'',''sys'',''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')
order by name ;
if (@Auth <> '''')
begin
exec sp_executesql @stmt = @Auth ;
end
go
'
from sys.databases
where name like 'xDB%'
order by nameJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply