There were already written few blog posts both about Row Level Security. I would like to add one more about this topic. I use this pattern for several years in SQL Server Analysis Services. Biggest advantage of this approach is, that you don’t have to fight with table relationships, which can be sometimes tricky to make work correctly. To follow with steps, you can download sample file, which uses Adventure Works Sample Data.
Let’s have a look at the report and scenario. Adventure Works sales products, that are grouped to categories. Common requirement is to limit data in report, so category manager would see just his/her category. So if I want to grant access just to “Bikes” I could write DAX row filter
[Category] = “Bikes”
This could work for few categories. But if you have 40. Role membership would be hard to maintain. Same thing can be handled by one role using configuration table.
For purpose of blog post, I will enter data manually using “Enter Data” option. You can replace data in table with real user names and email addresses. In real life I use for this configuration SQL Server Master Data Services, but any table would do.
Table security can stay unrelated
What I’ll need is to create calculated column to DimProduct table. Let’s call it Security_Category using function lookupvalue searching in security configuration table.