question

Gert-8236 avatar image
0 Votes"
Gert-8236 asked PRADEEPCHEEKATLA-MSFT commented

Azure SSAS: How to add and remove thousands of users from/to roles based on data?

Hi community,

I'm looking for a way that would allow us to add/ remove members to/from SSAS roles dynamically, i. e. I'm looking for a data-driven solution.

We have an Azure SSAS data model which we plan to roll out soon to a significant number of users (at least hundreds, more likely a few thousand).

Access to the model should be driven by an external database, let's say the HR database which holds all relevant information about an employee.

A simple scenario is that based on an employee's job, we'd like to assign them to a specific role. For simplicity, let's say we only want two roles in the data model:

  • Employee

  • Manager

Then, we would like to add all Managers to the "Manager" role, and all non-managers to the "Employee" role.

Furthermore, if somebody leaves the organization, we would like to remove that person automatically from all roles in SSAS. In other words, we'd want to keep the users in the data model in sync with the users in the HR database.

In a very simple world, I would hope for something like this:

Import a "User" table into the model that holds the email address and the role name:

User, Role
jane@company.com, Manager
john@company.com, Employee

Then, "somehow" I'd want the model to understand that the "Role" in that table refers to the "Role" in the data model. As a result, it would make Jane a member of the "Manager" role, and John a member of the "Employee" role.

I've seen suggestions to do this via XMLA scripts, but I do not see (understand) how this could be automated.

Users do not authenticate via Windows login, but by logging into Power BI online (app.powerbi.com). In the model, we can identify each user by their email address via this measure: UserName:=USERNAME()
We are using Azure SSAS (not on-premise).


Could somebody put help me out here and put me on the right track?

Thanks a lot in advance,

Gert

sql-server-analysis-servicesazure-analysis-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Gert-8236,

Just checking in to see if the below answers helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hello @Gert-8236,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.
Take care & stay safe!


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

Hi,

Where have you stored this list? I think you could try using Powershell script to do the adding.

Using Azure AS powershell comlet for adding one role member, then use "foreach" to loop over each row of the table. You could take reference to this code in PowerShell Code . It should be similar.

Perhaps there is other way, there is for now I can come up with.

Regards,
Lukas


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Did you make any progress? Have you resolved this issue ?

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-database-users should get you started on managing role membership programmatically. Note that for this scenario, there's no need to add the Roles table to the Tabular model itself.
With the Roles table in the model, depending on your specific security logic it could be possible to setup RLS off that table, in which case there would be no need to manipulate role memberships.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered

If your users are logging on to PowerBI.com that means you have AzureAD setup. And you can create groups in AzureAD, then add those groups to roles. So you could manage access through a few AzureAD groups.

The other option if you want to manage access using a data driven table base approach is to look at a "dynamic security" approach eg. https://www.kasperonbi.com/dynamic-security-made-easy-with-ssas-2016-and-power-bi/#:~:text=Dynamic%20security%20is%20one%20of,be%20part%20the%20data%20itself.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.