Chaining Permissions between Databases in SQL Server
On my recent SQL Server project we had a reporting database which contained nothing but views of the Data Warehouse database structures. I was asked to provide access to this database for users. I would expect this to be easy, but I have learnt that SQL Server can be a bit complicated.
It looked scary to use cross-database ownership chaining. All the articles in the internet suggest not to do it at all or at least understand all consequences of using it. I had to give it a good read, but it turned out that this solution would be ideal in our case. Decision on using ownership chaining depends on existing types of users with different levels of access to databases. In our case there were only three types of users across all databases on the server. They are admins, superusers and users. ETL developers fell into the category of admins. Analysts are superusers, they should be able to select data from any table in the Data warehouse. Users should only see the views we created for them. So in this situation ownership chaining is pretty safe as there’s no type of users who could breach the security and get access to something they are not supposed to have. It could lead to a hole in security in case if there are types of users who have some of create and execute permissions. If database chaining is enabled, these types of users would be able to access more things then it was intended with views.
This article describes all the steps in great detail. I have enabled chaining only between the Data warehouse and Reporting databases, not for the whole server.
[sql] ALTER DATABASE DataWarehouse SET DB_CHAINING ON;
ALTER DATABASE Reporting SET DB_CHAINING ON;
[/sql] User should be created on both databases as well.
[sql] USE DataWarehouse;
CREATE USER Phantom FOR LOGIN Erik;
CREATE USER Erik FOR LOGIN Erik;
[/sql] Nothing else is required for the DataWarehouse database. User should be granted with the required access to Reporting database objects, e.g.:
[sql] USE Reporting;
CREATE VIEW sales.Sales_2017_View AS
SELECT s.SaleDate, s.InvoiceNum, s.TotalAmount, s.ItemQuantity
FROM DataWarehouse.hub.Sales h
JOIN DataWarehouse.sat.Sales s ON h.h_Sales_key = s.h_Sales_key
WHERE SaleDate BETWEEN ‘2017-01-01’ AND ‘2017-12-31’
AND s.CurrentYN = ‘Y’;
GRANT SELECT ON SCHEMA :: sales TO Erik;
[/sql] Now Erik can query Reporting views in sales schema. However, if he would try to open the DataWarehouse database, he won’t see any tables and won’t be able to query anything.
If database chaining is not suitable in the organisation, there are other ways to manage user access in virtualised reporting layer. For example, you can create views on data warehouse database and grant users access only to these views. You’ll need to grant very specific permissions to each view, or if you grant user access to the whole schema, you’ll need to make sure that there’s no table under this schema that users are not supposed to see.
The option with the certificates for login looked more secure and reliable, but it looks more complex to implement. I would go for this option if I had enough time to get my head around how it works; maybe next time.