Retrieve List of Users and Roles in Environment using Power Automate

In a previous YouTube video, I have discussed how to Onboard Users to Power Platform Environments using Power Automate. This allows for a consistent and efficient process when onboarding these users. Now, once you have onboarded these users, how do you track which users have access and what is their related role?

In this post we are going to discuss how we can use Power Automate to programmatically retrieve user’s information and their security roles. We will then insert this information into Microsoft Excel where it can viewed, sorted, filtered etc. If you are just looking for a one-time snapshot of this information, you can generate a report from the Power Platform Admin center.

Let’s start with creating an Excel file and storing it in our OneDrive. Within the file, we will create fields for Username, First Name, Last Name, Email and Security Role. After we have our columns defined, we want to create a data table so that Power Automate can generate a schema for this dataset.

We will start with a manual trigger, but this can also be run on a schedule. Following our trigger, we will create a List Rows action that is part of the Dataverse connector. I renamed my action to be called Listrows-SecurityRoles. This is an important detail since we will use some custom expressions in upcoming actions.

The Table that we will connect to is called Users. We will want to select the following columns: domainname,firstname,lastname,internalemailaddress. These are the internal names of the columns within our User table. We want to include these fields because the payloads being returned from our query will include many unnecessary columns which may result in a payload being generated that is too large for our connector.

Users and their related security role permissions, have a many to many relationship. As a result, we need to set an Expand Query property to include systemuserroles_association. This will create a node in our payload that includes all the security roles that belongs to this user.

Power Automate will throw an error if we don’t provide a Row count value. Here you should provide an appropriate value based upon your scenario. I set my value to 1000.

We now need to add 2 loops. The first loop will loop through each record returned from Listrows-SecurityRoles action that we discussed in the previous step. The inner loop is going to loop through each role that is returned from our Expand Query. Within this inner loop we will then add a record to our Excel spreadsheet.

Note: For our Security Role field we have to write an expression to retrieve that value. You can use the following: items('Apply_to_each_2')?['name']. This expression assumes your inner loop action is called Apply to each 2. If you rename that action, update this expression accordingly.

Testing

We can now run our flow and will see the following result. From here, we can filter, sort and even load into a Power BI dashboard.

Conclusion

For organizations that are growing their adoption of the Power Platform, managing access becomes an essential governance activity. Using this approach, we can keep track of who has access in each environment. To see a video version of this content, please check out the following YouTube video: Retrieve List of Users and Roles in Environment using Power Automate.