Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MCassady
Helper I
Helper I

Pass website permissions to embedded report

I have a report embedded in a website page.

 

There are two SQL tables, which control what buildings/data a user on the website can see/access.

 

1. The User table holds things like user name, encrypted password, contact info.

2.  The UserSites table links those users with which buildings they will see on the website.

 

Example:

 

User

 

UserIDUserNameEmail
1Bobbob@bob.ca
2Johnjohn@john.com

 

 

UserSites

 

UserIDBuildingID
110
111
112
210

 

Based on this data, Bob would be able to see building 10,11, and 12, but John can only see data for building 10.

 

Is there a way to pass this relationship into an embedded powerbi report, to limit what the authenticated user sees based on their login from the parent website?

 

I have read that row level security may help, but I think I would have to set it up, and republush the report every time we get a new user or new building. This seems like too manual of a process.

3 REPLIES 3
TomMartens
Super User
Super User

Hey @MCassady ,

 

you have to use Row Level Security but you can make it dynamic by using the function USERPRINCIPALNAME.
Next to that you have to create a relationship between the user and the usersites table.
Of course, you have to make sure that both tables are containing the proper data, meaning new users must be added to the user table and the usersites table has to be adapted as well.

If this is given each data refresh makes sure that the user are seeing what they are allowed to see.
Hopefully, this provides an idea on how to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I think I've figured it out. I have to have the role also checked, not just the "Other User"

MCassady_7-1671086762305.png

 

Is there a way to set up RLS JUST for "other user." That way we do not have to add/remove employees roles as they come or go? It'd be nice to just have them in our User SQL table, as they will be in there without any extra powerbi steps.

 

Hey @TomMartens ,

I am trying to set this up as you suggested, however I am having trouble testing it.

 

I created this relationship:

 

MCassady_5-1671086384599.png

As well as the following RLS:

 

MCassady_4-1671086256478.png

 

And here is my test data:

 

 

User:

MCassady_2-1671086132883.png

 

UserSite:

MCassady_3-1671086150509.png

 

 

However, when I click "view as 'Other User'", and use an email that IS NOT in this list... I am still able to see the data on the screen. Am I doing something wrong?

MCassady_6-1671086565319.png

 

 

When I enter in cassady@cassady.com, I would expect the dashboard to only allow me to see data for TestSite1.

 

 

Any ideas?

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors