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
Nishantjain
Continued Contributor
Continued Contributor

XMLA end point and row level security (RLS)

Hi,

Is it possible to manage row level security in power BI service using XMLA end point with SSMS or something similar?

I would like to create our amend roles, add or remove users etc

Thanks
13 REPLIES 13
GilbertQ
Super User
Super User

Hi there

Currently the XMLA end points are read only.

It is planned to allow for writing to the XMLA end point, which will then mean that yes you can add or remove roles and users and or groups.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Nishantjain
Continued Contributor
Continued Contributor

Thanks that's great.

Does this mean that I can atleast read the current roles and users right now

Yes that is 100% correct you can read anything from the XMLA end point




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Nishantjain
Continued Contributor
Continued Contributor

Great. Can you point me to any boog/documentation how to do that?

Hi @Nishantjain 

 

Have a look at this blog post

 

https://powerbi.microsoft.com/en-us/blog/power-bi-open-platform-connectivity-with-xmla-endpoints-pub...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Nishantjain
Continued Contributor
Continued Contributor

@GilbertQ 

 

Thanks for the link. I have gone through the blog and it explain how to connect using XMLA endpoints. Do you know how to extract the information once I have connected using SSMS?

 

Thanks

Hi there

You can connect with Excel, I have not created a blog post on how to do it yet, but it is in my plans




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Nishantjain
Continued Contributor
Continued Contributor

Thanks.

Managing RLS is very manual in the PBI service and is not helpful in our organisation which has over 30000 employees. If you can recommend anything that would be great.

Thanks

Hi there

There is Dynamic RLS in which you can define a single role and the RLS is applied dynamically where you can do it via AD Security Groups

Here is my blog post on how to achieve this: https://www.fourmoo.com/2018/02/20/dynamic-row-level-security-is-easy-with-active-directory-security...




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Nishantjain
Continued Contributor
Continued Contributor

@GilbertQ 

 

I have already implemented Dynamic RLS where possible, but in most cases it is not a feasible solution. Simply because it requires a data set to be refreshed. It is not possible to refresh the dataset everytime we add a new user to the dataset.

 

Nishant 

Hi there

That is by design as far as I know.

I would also set the expectation with the business that new users will be added once a day, when you can refresh the dataset?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Nishantjain
Continued Contributor
Continued Contributor

I appreciate that this is by design.

 

I was hoping for more of an automated solution to manage users. 

 

I have looked into using SSMS 18.0 and AMO but neither seems to be allowing me to manage roles. I hope there will be something in the near future to automated RLS. Given that the team is focusing on bringing new features specially for large Enterprises, I am surprised that this hasn't been look at. 

 

Thanks for your help @GilbertQ 

Hi there

If you do use AD Security groups you can have it all automated, but as always with any technology you will have to get the data updated in order to get new changes applied.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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