Ownership chaining

  • Hi, I have four views on one database to which one sql authenticated user has been granted select but nothing else.

    This user has no permissions across any other database on the instance, or, any other object on the view's database.

    Account can successfully select from views when views reference objects on the same instance.

    When the views reference tables on another database on the same instance an error in thrown.

    Msg 916, Level 14, State 2, Line 2

    The server principal "user" is not able to access the database "database" under the current security context.

    I turned on database chaining on both databases. But that made no difference. (I did not restart the service, should I?)

    Any ideas how I solve this please?

    Thank you in advance for any contributions.


    All the best,

    Duncan

  • This was removed by the editor as SPAM

  • Ownership chaining doesn't work cross database unless the cross database chaining feature is enabled on both databases and the login in question has a user in both database. Cross database ownership chaining, however, does also come with several security considerations, as a user with high permissions in one database can (abuse) those permissions to elevate their permissions in another database.

    • This reply was modified 1 year, 3 months ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thanks for the responses, I appreciate it.

    I set db_chaining across both databases to on.

    Added the SQL authenticated account to the public role on the target databases.

    on selecting from one of the views I got this error.

    Msg 229, Level 14, State 5, Line 2

    The SELECT permission was denied on the object 'table name', database 'name', schema 'dbo'.

    Does this mean I will have to grant select permission across all the tables on the target database to the account?

    Thanks in advance for any ideas.

     


    All the best,

    Duncan

  • This implies that the ownership chain is being broken somewhere; the point of ownership chaining is that you don't need to give explicit access to the objects. Who are the owners of the databases in question? Are they the same as the objects owner?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

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

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