We have two databases which contain a series of tables and views. The views select data from the tables in their own individual database. All objects are in the dbo schema; we don't use other schemas. The same users exist in both databases, and are members of an identical role which exists in both databases. In both databases, the role has been granted SELECT permissions to the views, but no permissions to the base tables themselves. This all works very well; in each database, the users effectively can access data through the views, but not from the base tables.
However, now we have a new view in database A which selects from base tables in database B. Granting the usual SELECT permissions on this view does not work -- the users get an error saying the SELECT permission was denied on the table in database B.
I suspect this is a cross-database / ownership chaining issue, but I can't find a proper solution. I tried setting the TRUSTWORTHY and DB_CHAINING options in both databases as a scattershot approach, but it didn't change the error message. I don't want to grant permissions to the base tables in either database, although I suspect that may get around the error.
How can I grant these permissions (and/or configure the system) so that the users can still only SELECT from the views and not the base tables in both databases?
Thanks in advance!