photo by kerttu

It’s typical in Business Intelligence that users are not getting data from the Data Warehouse database directly. Although the table structure should speak to them, one should be careful when querying Slowly changing dimension type 2 tables to get only point in time records (most often it’s current records). Surrogate keys or any other technical fields quite often got misused for SQL query filters by inexperienced users. Also there might be a security requirement for users from different departments to see only a specific set of the organisation’s data, which could be hard to manage within one database that is designed to combine all the data. To accommodate all of these requirements, a reporting layer of the Data Warehouse is created to provide all users with the right access to the right data. Also with the modern day technology, there is a tendency to virtualise this layer. Presuming that all the data has been integrated and cleansed in the data warehouse, the Data Mart only needs to present the data and do simple calculations in memory if required.
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;
GO
[/sql]User should be created on both databases as well.
[sql]USE DataWarehouse;
GO
CREATE USER Phantom FOR LOGIN Erik;
GO
USE Reporting;
GO
CREATE USER Erik FOR LOGIN Erik;
GO
[/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;
GO
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’;
GO
GRANT SELECT ON SCHEMA :: sales TO Erik;
GO
[/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.
Kate
Data masseuse