Direct Query and Row Level Security - Do Viewers need to have read access to SQL Server database?

[TUNINH]

Hello all,

I have a question regarding the viewer authentication in PowerBI report using DirectQuery and having Row Level Security applied when it's published in a PowerBI Workspace/PowerBI Service.

I am trying to create a PowerBI report using Direct Query storage mode, with RLS applied using USERPRINCIPALNAME() in a dimension table to filter the data in fact tables related to viewers, and there is an enterprise power bi data gateway with a service account authenticated to SQL Server database.

So in this case, I wonder whether all viewers are required to have read access to the SQL Server database, so they can see the report with filtered underlying data on PowerBI workspace or not. Or the SQL Server database will use the authenticated service account from the Enterprise Data gateway to run the queries and return the filtered data of the viewer?

Because the data in SQL Server database is restricted to give read permission to many users, I wonder if there are any solutions that allow viewers (who don't have the permission to SQL Server database but have the view permission to PowerBI Workspace) to view the PowerBI report with filtered underlying data.

Many thanks.

[v-cgao-msft · Accepted solution]

Hi @TUNINH ,

According to your description, you don't need to grant SQL Server database read permission to every Viewer, they will connect to the data source through the gateway using the credentials you configure. If configured correctly, each viewer will use the credentials you configure to connect to the data source and send filtered queries according to the Row Level Security rules you configure to get the data.

Special attention, if you have configured a classic workspace so that members have edit permissions, the RLS roles won't be applied to them. Users can see all of the data.
If you publish your Power BI Desktop report to a new workspace experience in the Power BI service, the RLS roles are applied to members who are assigned to the Viewer role in the workspace. Even if Viewers are given Build permissions to the dataset, RLS still applies. For example, if Viewers with Build permissions use Analyze in Excel, their view of the data will be protected by RLS. Workspace members assigned Admin, Member, or Contributor have edit permission for the dataset and, therefore, RLS doesn’t apply to them. If you want RLS to apply to people in a workspace, you can only assign them the Viewer role.

Here are some references that may be helpful.

Row-Level Security for Cloud models and DirectQuery in Power BI

Row-level security (RLS) with Power BI
Add or remove a gateway data source

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[TUNINH]

very appreciated your detailed response 👍