Database Administration
sql-server sql-server-2016
Updated Mon, 29 Aug 2022 07:19:51 GMT

The SELECT permission was denied - View querying data from a different database

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?


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.