Post reply

Problem with Cross DB Ownership Chaining

  • May 15, 2009 at 3:27 pm

    #204951

    Hi, i recently moved my databases from server using dettach and attach moving the files throught the network, everything worked fine except for one of this databases which gives me problems while using the Cross DB Ownership Chaining on SQL 2000 SP3. Everytime i try to give permissions to an object that references this particular database (named Projects) users wont get the inherited permissions too. I don't know why this happens if ALL my databases have sa user as owner and all objects have dbo as owner, if I have a view for example which has:

    CREATE VIEW dbo.v_example

    AS

    select * from Projects.dbo.table

    And I grant select (having Cross DB enabled and running under configurations) to a user "user1" for example on v_example, users recieve the following message:

    'Select permission denied on Projects.dbo.table'

    While if the view had reference to a table in any other database it works, even though as I said all have sa as owner and all tables have dbo as owner.

    What could be happening?

    THanks!

  • May 19, 2009 at 11:07 am

    #997171

    Hi guys

    No one has replied, I'm wondering if I didn't made myself clear or it is a very weird error you can't figure how to solve?

    Thanks

  • May 19, 2009 at 11:53 am

    #997185

    If you have the problem with the user permissions you better use sp_change_user_login autofix option.


    - Pradyothana     http://www.msqlserver.com

  • May 20, 2009 at 7:31 am

    #997581

    after dettach and attach db, even in same instance, you need to reenable cross-db ownership chaining.

  • May 20, 2009 at 10:50 am

    #997772

    After attaching the db i tried running this:

    sp_configure 'Cross DB Ownership Chaining',1

    RECONFIGURE WITH override

    but it didn't helped, I also runed this:

    sp_change_users_login 'AUTO_FIX','user'

    On the user that I'm granting select permission on a view wich references with select a table on the database that has the problem but no.

  • May 20, 2009 at 11:18 am

    #997787

    i guess that in sql 2000, you need to make sure the user has select permission on underly table if it belongs to different user.

  • April 7, 2010 at 4:33 pm

    #1147850

    The database owners should be same for bothe databases

    Use sp_changedbowner "sa" assuming that "sa" is mapped to "dbo" and each object in your database is owned by "dbo"

    Hope this helps

    Sudheer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply