Re: Cross DB ownership chaining (SQL2005 SP3)



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 :)


.



Relevant Pages