Re: Cross DB ownership chaining (SQL2005 SP3)



The owner_sid is the same as before the change.... but the chain is working !

My guess is that toggling the database owner fixed a mismatch between the sys.databases owner sid and the dbo user sid in sys.database_principals. See http://weblogs.sqlteam.com/dang/archive/2008/01/13/Database-Owner-Troubles.aspx for a script to identify the problem databases on a server. Note that you should use ALTER AUTHORIZATION in SQL 2005 and later versions.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Olivier HUET" <ohuet_news@xxxxxxxxxxxxxxxxxx> wrote in message news:mn.843c7d9745ff050b.104751@xxxxxxxxxxxxxxxxxxx
I've found the solution :)

I've seen the messages of Jeff and Erland 14/05 and 15/05 and so I've checked the owner_sid of my DBs.

It was the same, so it should be good, but I still try sp_changedbowner on my dbs.

SP_CHANGEDBOWNER 'sa'
SP_CHANGEDBOWNER 'previous_owner'

The owner_sid is the same as before the change.... but the chain is working !

I don't know what is different now and what sp_changedbowner exactly did, but thanks a lot to Erland (and Jeff) for the idea :)


Hello all,

I have 2 DB, with the same owner.

In the first DB, I have a table with dbo as schema.
In the second DB, I have a view that read the table of the first DB and also using dbo as schema.

I've run this query to enable cross db ownership chaining :
ALTER DATABASE DB1 SET DB_CHAINING ON
ALTER DATABASE DB2 SET DB_CHAINING ON

I've created a user with db_owner privilege on DB2 and public access on DB1.

But when I do "SELECT * FROM MyView", I get the error message "The SELECT permission was denied on the object 'MyTable', database 'DB1', schema 'dbo'."

Of course, If I give the select privilege to my user the view work fine, but my user should not have any privilege on DB1...

Does anybody see what I've missed ? Or what I should check ?

(I've also tried sp_configure 'Cross DB Ownership Chaining', 1 ; reconfigure ... but it change nothing)

Thanks a lot in advance :)



.