Row Level Security in Power BI Report Server

All right, here's my best attempt to walk through it.  For this example I’ll be using the AdventureWorksDW2012 database.

For my hypothetical case, I’ve got regional sales directors that should only be able to see information for employees that are in their assigned sales territories.  I’m going to implement that by putting a security policy on the DimEmployee table.  In summary the steps are:

1. Create and populate a new table that defines which sales territories a particular user (as defined by their Windows login ID) is allowed to access.
2. Create a new function in SQL Server that checks the relationship between user and allowed sales territories.
3. Create a new security policy on the DimEmployee table using the function created in step 2.

Once those steps are done I can create a report in Power BI that accesses the DimEmployee table and take advantage of the implemented RLS.

In more detail:

1. Create a new table that defines which sales territories a user may access.  I called my table SalesTerritoryRLS.  It’s a simple table with two columns that looks like this:

The NetID column is simply the Windows user ID of the user, and SalesTerritoryKey correlates to the column SalesTerritoryKey in the DimEmployee table.  So as currently populated this table says that I (davidm5) am allowed to see data for sales territories 1 and 4; the user ‘SomeOtherUser’ is allowed to see data for sales territory 2.

1. Create a new function in SQL Server. The SQL to create the function looks like this: 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[fn_SalesTerritory_SecurityPredicate] (@salesTerritory AS int)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

       SELECT 1 AS fn_SecurityPredicateResult

       FROM dbo.SalesTerritoryRLS rls

       WHERE rls.SalesTerritoryKey = @salesTerritory

              AND rls.NetID = SYSTEM_USER

GO

We’ll apply this function as a filter in the next step.  Basically this function checks to see if the sales territory on a row it’s evaluating is one of the sales territories the currently logged in user is allowed to access.  That’s where SYSTEM_USER comes in – see the final line of the WHERE clause.  SYSTEM_USER is the ID of the currently logged in user.

1. Create a security policy using the function. The SQL for this is:

CREATE SECURITY POLICY [dbo].[dimEmployeeFilter]

ADD FILTER PREDICATE [dbo].[fn_SalesTerritory_SecurityPredicate]([SalesTerritoryKey]) ON [dbo].[DimEmployee]

WITH (STATE = ON, SCHEMABINDING = ON)

GO

This ties the function created above to the DimEmployee table.  When you do any SQL operation on the table, it passes in the SalesTerritoryKey for each row and the function is used to determine if it’s a row the user is allowed to access, based on the values in the RLS table created in step 1.

Now you’re free to access DimEmployee in your Power BI report and RLS will be enforced.  Note that for this to work you MUST use ‘Windows Authentication’/’As the user viewing the report’ on the Data Source Settings for the report.

Notice that creating the function and applying it as a security policy are two different steps.  That means you could apply that same function to multiple tables to implement our RLS on sales territory across multiple tables if you’d like.

Also be aware that this means RLS is applied at the data layer; for any tool the user might use to access the DimEmployee table, RLS will be applied.  Even if I log into SSMS directly to query the DimEmployee table, I’ll only be able to see rows where the sales territory is 1 or 4.  And the way I wrote this particular function means that by default users get NO access to DimEmployee data; until a row is entered for them in the SalesTerritoryRLS table, they won’t be able to see any DimEmployee data.