Ownership chaining

Question

Post reply

Ownership chaining

Duncan Lawrence

SSCrazy

Points: 2047
More actions
February 6, 2023 at 8:08 am

#4145946

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
February 6, 2023 at 9:05 am

This was removed by the editor as SPAM

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

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

Ken McKelvey SSCoach Points: 18768 More actions · Answer 1

https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/

https://www.sommarskog.se/grantperm.html

Thom A SSC Guru Points: 99126 More actions · Answer 2

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, 2 months ago by Thom A.

Thom~

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

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 3

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

Thom A SSC Guru Points: 99126 More actions · Answer 4

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