Bulk add security groups or users to audience permissions and RLS across hundreds of models/apps

[rappleton]

Hi, 

Does anyone know if we can bulk add;

1. hundreds of security groups to RLS settings across ~200 models
2. hundreds of security groups to audience permissons across ~100 apps?

Is there something using PowerShell or the REST API? I couldn't find anything at all. How do large companies manage group access to these features at scale when there is organisation change etc? From a tenant admin point of view this must be available surely?

Any help much appreciated, 

Thanks a lot, 

Richard

[lbendlin]

Usually distribution list management is done by a dedicated enterprise application, including onboarding, auditing and offboarding.

Can you grant access to "super" PDLs in Power BI that contain the other security groups?  If not then consider using dynamic RLS instead.

[rappleton]

Hi @lbendlin thanks for your quick reply, sorry I failed to give more context to my scenario. 

We have a good solution in place for onboarding/offboarding, and the RLS on our models is configured correctly. We are completing an excercise to replace current security groups in 'Windows Server AD' and replacing with new 'Cloud' Azure AD groups.

So the infrastructure is in place I just need to bulk add the new security groups to the RLS in a couple of hundred models, and also bulk add the new security groups to ~100 audience permissions within the app. (and then remove the old security groups from both).

Do you know if this is possible?

Thanks a lot for your help. 

Richard 

[lbendlin · Accepted solution]

Have a look at the DMVs.  Dynamic Management Views (DMVs) in Analysis Services | Microsoft Learn

especially https://learn.microsoft.com/en-us/openspecs/sql_server_protocols/ms-ssas-t/f1f9923f-de9c-48ad-b810-6...

And then use Add SSAS Role Members via PowerShell or XMLA (mssqltips.com)

[rappleton]

Thanks a lot @lbendlin I will take a look 😁