SQL Server – Chain Permission

access-controlpermissionssignaturesql serversql-server-2012

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?

Best Answer

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:

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
Related Question