Restricting Access to Underlying Tables in Different Databases While Allowing View Access

Hi, the short answer to your question is no.

The long answer is:

The moment you write a cross-database view or procedure, the permissions required to use that item become far more complicated than read-write for just that database.

You will have to determine the lowest level of access that can be granted to enable a user to pull from a cross-database view, or call a cross-database stored procedure.

Some objects will allow you to view dependencies - that might help, but it will only tell you what objects it references - it won't tell you whether the account needs read or write permission to those objects. In terms of a stored procedure, you will have to determine that, based upon your understanding of the procedure.

The neater solution would be to use a replication tool to replicate the data you want into your main data warehouse database. This would eliminate the need to consider cross-database permissions or roles.