SQL Server Chain Permission We have the 2 databases.


Reporting
HR


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?
Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft.

Instead, you should create a certificate that can be used to sign modules and act as a proxy for permissions between the databases. The catch is that they work on stored procedures, function (non-Inline TVFs), assemblies, and triggers. But this method allows for those Users that should not have any access to the [HR] database to actually not have any access themselves: it is the code (i.e. the module) that has the access.

The basic concept:


Create certificate in [Reporting]
Create stored procedure in [Reporting] (that selects directly from tables in [HR])
Use ADD SIGNATURE to sign the new stored procedure using the certificate
Backup the certificate to a file
Create certificate in [HR] FROM the backup file you just created (the certificate needs to be identical in both places!)
Create user in [HR] FROM the certificate
GRANT SELECT permission on appropriates tables in [HR] (i.e. the ones references in the new stored procedures in [Reporting]) to the new certificate-based user
Go home and enjoy the rest of the day :)


Some resources:


Module Signing Info
Ownership Chains
ADD SIGNATURE
Signing Stored Procedures in SQL Server
Tutorial: Signing Stored Procedures with a Certificate
Tutorial: Ownership Chains and Context Switching


OR, if you prefer to stick with the simplicity of using views, then your views are in the wrong database. You need to instead:


Add the appropriate User(s) to the [HR] database so that there is basic access, but do not grant any permissions to any tables (and hopefully no group or role membership will imply such permissions)
Create the desired Views in the [HR] database
GRANT SELECT only to these Views, and not to anything else, to these Users