Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Has anyone had any luck implementing RLS in Azure SQL DB and having it work in Power BI reports? I was able to implement and get the desired result set in Azure SQL DB. However, I am not able to get any rows using the same account in PowerBI service\premium using directquery on a basic report using only one table. I cannot see anything in PBI Desktop either, but that is expected since it uses a domain account and Azure SQL DB uses the O365 UID. When I set the security policy below to off then I get all of the rows for the table in Power BI without publishing again. Below is brief description of what I have done.
Setup a Mapper table
Create Schema
CREATE SCHEMA Security;
Create Function
CREATE FUNCTION Security.fn_ChkSavPredicate(@CurrentOfficerCode as varchar(255))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccesResult FROM dbo.Mapper
WHERE ([CurrentOfficerCode] = @CurrentOfficerCode AND [User] = USER_NAME());
Create Policy
CREATE SECURITY POLICY ChkSavMap
ADD FILTER PREDICATE Security.fn_ChkSavPredicate([CurrentOfficerCode])
ON [dbo].[ChkSav]
WITH (STATE = ON);
Validate
Execute AS USER = 'account@domain.net'
Select * from ChkSav