Dynamic RLS solution is not working for newly added users

[Sajna_M]

Hi All,

I created a Power bi solution which integrates with SQL server which stores the user transactional data, Company mapping table, Roles, Participants information.

In Roles table RT, I have column like RoleID, RoleName

In partipants table PT, I have column like RoleID, Username, email Id

In user transaction table UTT, I have columns like ticket ID and company name

In company mapping table CMT, I have column like company name and RoleId

Step 1:

Created a relationship between transaction data and permission master data based on company name and RoleId

Step 2:

We have created a RLS and added a condition in user transaction data as lookupvalue(UTT.companyname,  UTT.Companyname, UTT.Companyname, PT.emailID, USERNAME())

Step 3: published and added users into roles (RLS part)

Output: it started to give right output for role users with right level.

Problem: when we add new user in SQL database and refresh data set.  For the newly added users, the respective transaction data is missing. Seems like, we have to publish the report again to apply for the newly added user to view their respective data.

So far I tried with manual refresh, not with schedule refresh. Accoridng to me, both gives the same set of data only, but it matters with time of refresh.

Is it right? Is there any solution for this? I wish to see this by refreshing dataset.

Regards!

[v-shex-msft]

HI @Sajna_M,

AFAIK, current dynamic RLS support to assign roles to security group members.

In my opinion, I'd like to suggest you try add these users to a office 365 security groups then assign roles to that groups.

It will batch add roles to whole group members so you can simply manage roles for new members.

Row-level security (RLS) with Power BI

Add members

You can add a member to the role by typing in the email address, or name, of the user, security group or distribution list you want to add. This member has to be within your organization. You cannot add Groups created within Power BI.

Create, edit, or delete a security group in the Office 365 admin center

Otherwise you need to manually assign roles to new users each time after finish add users to your database.

Regards,

Xiaoxin Sheng

[Sajna_M]

Hi Xiaoxin Sheng,

My problem is not adding user in role under PBI service, I can add user or groups here. And I don't mind adding the new user again and again.

My Scenario is I added a user Joe.xyz for a company XYZ in SQL dataset and company role in dataset secuirty permission page.

I refresh the dataset, now my expectation is Joe.xyz should able to view company data XYZ. But the actual is, Joe is unable to see the data of XYZ.

To fix it, I goto desktop version and refresh the data and publish it again, then it is working for Joe.

So the question is when we refresh the dataset in power bi service, why Joe is was unable to view the report  data of XYZ?

Regards,

Sajna M

[v-shex-msft]

Hi @Sajna_M,

What's wrong with joe ? Can't view records, show error or other? In addition, have you try use 'test with role' feature to test on power bi service?

BTW, I'd like to suggest you to check detail records to confirm if these records has update to power bi service dataset.

Regards,

Xiaoxin Sheng

[Sajna_M · Accepted solution]

Hi @v-shex-msft,

I found the issue, manual refresh of dataset in service was failing.

There was no error in gateway integration. I removed the connection from gateway and readded, then the refresh the dataset and it started to work.

Now Joe is able to see his data as well in dynamic RLS report.

Thanks!