Row Level Security doesn't work with e-mail aliases

[KevinColes]

Hi experts,

I have set up a report that uses Row Level Security and roles that filter on the user's login using the USERPRINCIPALNAME() function. In Power BI Desktop it is functioning as expected and I can test as various users under the one role I have set up so far and I've had no issues. 

When I published to the Service and setup the security and added users to the roles it didn't seem to work and then after some searching I found out that RLS works only works on View Only roles, so I changed that and still got weird behaviour. No filtering occurred at all when I viewed as a user and my "Logged in as: person@domain.com" measure I created wasn't changing from my login.

I then discovered by way of trying a few different users, that only specific users weren't working and that some did work exactly as I would expect. All of the non-working ones had legacy logins in the form of "firstname@domain.com", while the majority of the users had e-mails/logins in the form of firstinitiallastname@domain.com.

I spoke to IT and they confirmed that these are legacy users who originally had just firstname e-mails and they're now aliased to the first intial/last name addresses. 

I was able to adjust my query to pull those users into my Employee table in the legacy format. When I try testing as them in the Service, the auofill picks them up in the same format, but the filtering doesn't work. 

I have tried using their proper e-mail format as well, but in the service you cannot select that format for testing (or for adding to security roles, etc.) 

I am meeting with the IT group today to look into their accounts (as I don't have access) but I'm wondering if anyone else has come across this issue. I know they will not want to start changing these accounts (there's probably 25 or so), so I need to find a way to make them work.

Thanks in advance!

Kevin Coles

[KevinColes]

So I did a bit more testing on this with IT and it seems the legacy logins are not all the same. Some would filter my "Logged in user:" measure correctly however it would filter the records completely instead of having it unfiltered altogether.

I can't help but think that the simplest solution would be to build my RLS role filter in such a way that it looks for a match on either of two columns: Email and LegacyEmail (which I will create). So evey employee would have a standard e-mail and some would have a Legacy one. 

My current filter is like this:

Here I filter my Project table where the ProjMgr (numeric for EmployeeID) matches the EmployeeID in my Employee table where the Email field matches the logged in user. Can anyone help me add some OR logic to this so that it looks for a LegacyEmail first and if doesn't find one, defaults to the Email field?

Thanks,

[GilbertQ]

Hi there

One other solution I would think of is in Power Query, where you know which of those 25 accounts are different to use a conditional column and where it is one of those accounts use the legacy email address or keep it as the current email address.

By doing it in this way you will not have to create a complex DAX measure to try and find the right value?

The right email addresses will all be in one column.

[KevinColes]

Hi @GilbertQ 

I am already doing as you suggest... my data is coming from an ERP system and the users standardized (jdoe@) email is in there and I have set up a separate table of legacy users.....in my query I do a case statement whereby if a legacy e-mail exists, use it, otherwise use the e-mail.

This is not the issue because I have tried this both ways - passing the legacy e-mail and the standard version. The issue is in the Service because I have tried testing using View As some of the legacy users and I know they are using the legacy e-mail that matches what the Service is pulling. I think what is happening though is that on those users, their Primary Email address is jdoe@ even though the service autofills to joe@. 

Any user with a Standard e-mail works.....legacy do not. 

Thanks,

Kevin

[GilbertQ]

I would then chat to IT to see what they can do to resolve the issue.

I would imagine that all the emails should be the same throughout the company?

And that would then solve your issue!

[KevinColes]

In a perfect world yes...but it's my client not my company so I can't really dictate. Also the users are accustomed to the format they have so I'm not sure the appetite is there to change it now. 

Kevin