We have the 2 databases.
There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database.
So, we created the special views in Reporting database and the users have dbowner right for that Reporting database.
But, when they try to run the query, the error message is showing...
The server principal "test" is not able to access the database "HR" under the current security context.
When I grant them as the db_datareader reader for HR database, it's all fine and they can run the query. But it breaks the security and we don't want them to get access to the HR database directly. That's why we made the special views in Reporting database.
How can I enable those users to run the view/query without giving direct access to the HR database?