2

I created a view SchemaX.VW_VIEW on a DatabaseB. This view is querying data from another database DatabaseA of the same server.

I have granted the SELECT permission on VW_VIEW to a user. However when the user tries to query the view, it says "The SELECT permission was denied on the object 'OriginTable', database 'DatabaseA', schema 'SchemaY'.

SchemaY and SchemaX are both owned by 'dbo' so there shouldn't be any break in the ownership chaining.

How can I fix this issue without granting read access to the underlying tables?

1
  • @DanGuzman that ^ should be an answer, not a comment, methinks. – mustaccio Jan 10 '20 at 13:57
1

To maintain an unbroken ownership chain among databases with views, the databases must be owned by the same login in the case of dbo-owned objects. Also, the DB_CHAINING option needs to be enabled for the databases involved. The user needs connect permissions to all databases accessed by the view but only permissions on the view need to be granted.

Be aware that of the security implications with DB_CHAINING. Only enable the option when you fully trust users who can create objects in databases with chaining enabled. This is less of a concern when only sysadmin role members can create objects and review scripts before deployment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.