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



.



Relevant Pages

  • Re: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... Does it exist in the master database? ... Users can be put in the dbo role, but dbo will still own objects ... concept of 'schema' as 'owner' rather than just schema as DDL is exploited ... to the appropriate Fixed Server Roles or whatever is applicable. ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... system_function_schema -especially in the master database. ... Most good judgment comes from experience. ... dbo is in the context of a database, and can be the schema owner for the ... Users can be put in the dbo role, ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
    (microsoft.public.sqlserver.server)
  • Re: Confused about dbo
    ... Jasper Smith (SQL Server MVP) ... > database within a SQL Server instance. ... > There are several ways you could have the username dbo. ... > the true owner of the database. ...
    (microsoft.public.sqlserver.security)