As you are reading this post, you are one of two people. The first group are saying “Wow – I never thought to use existing Microsoft 365 Groups to drive my Row Level Security!” The other group is screaming “Microsoft 365 Groups don’t work for Row Level Security!”
Technically, the second group is correct. If you try to apply a Microsoft 365 Group for Row Level Security, you will discover that it is not available. This is very frustrating for a number of report creators. But as with all the great solutions, necessity is the mother of invention.
The Scenario
In the last month, I have been approached by two customers about how to leverage existing Microsoft 365 Groups in Power BI. When talking to these customers, they built out their security model using these groups. With Microsoft heavily relying on groups to drive security, it throws a wrench in things. And unlike SharePoint which allows you to leverage existing Active Directory groups, Teams does not allow you to do that.
So you bought into Teams and started using it to allow self service collaboration. Your users are successfully updating memberships with no issues. But now you need to create a security group and replicate it. Doing it manually takes time and it is frustrating at best. Plus it will likely lead to multiple service desk calls when it is not working as planned.
And while I am not lazy, I like to make simple solutions that enable users. So let’s automate the solution to make it easier!
Power Automate to the Rescue
Power BI and Power Automate are part of the Microsoft Power Platform. While powerful alone, they are better together when it comes to creating solutions. Power Automate enables end users create self-service automation solutions. We can quickly build a flow that will automate our replication process.
The concept is simple – we will create a flow that monitors a Microsoft 365 Group membership and update our security group. You might have looked into Power Automate in the past and decided to not use it because of licensing costs. But the best part this solution is that you can use the free tier of Power BI to enable the solution!
Implementing Microsoft 365 Group Replication
We could create the flow from scratch, but why do that when I have already built it! This will help you save some time and quickly re-deploy it for other groups.
Step 1 – Create a Security Group
To get started, you must create a security group that you will use. Since I am 100% cloud based, I go to the Microsoft 365 Admin Center to create the group. Go to Teams and Groups on the left hand navigation and select Active Teams and Groups. From there, create a new group. Select the Security group type and click next. Give your group a name and finish up the group creation.
Once created, go ahead and open the group. Make sure whatever account is creating the flow in Power Automate is added as the group owner. Lastly, before you close the window, copy the group ID out of the URL as you will need it shortly. You can accomplish the exact same task in Azure Active Directory, but I feel like this is easier.
As a point of convenience, you might want to add the existing members of the Microsoft 365 Group into the security group. This will need to happen anyway, so why not do it now?
One last thing – if the account creating the flow is not already a member of the Microsoft 365 Group, make sure they are added before moving on to the next step!
Step 2 – Import the Flow
First, download the prebuilt package I created. This will save us some time with the deployment of this solution and will make it easier for you to replicate.
One you have downloaded the package, login to Power Automate using your Microsoft 365 Credentials. Once signed in, go to My Flows on the left, select Import, and pick Import Package. Click upload, pick the package you downloaded, and click OK.
Once you have loaded the package, you need to update the connection details for the Microsoft 365 Groups connector and the Azure AD connector. Click on Select during import and specify which account to use. Click Import when you are finished
The import will take a few moments. You will receive a message when it is finished. Once the import is complete, click on Open flow.
Next, we need to update the flow to use the groups you specify.
Step 3 – Specify the Microsoft 365 Group and Security Group
Updating the Microsoft 365 Group is really easy. Just clear out the existing content and select your group from the drop down. It is easy as that!
For the security group, it is a little harder. All you need to do to is add the group id you copied when you created the group in our variable. Just set the value field for Initialize Security Group ID Variable block. We are assigning it to a variable because we need to use it with two actions. I don’t have to do it this way, but I like it because I only have to update it once. This way I don’t miss anything!
Make sure you save your work before moving on to the next step!
Step 4 – Test Your Flow
Now that everything is in place, go ahead and test your flow. Add a new user to a Microsoft 365 Group. It will take a minute or two, but it should appear in the security group.
Once it shows up, you can remove the user from the Microsoft 365 Group. Again, it will take a minute or two, but it should be removed from the security group.
If it passes the test, you are all set! Congratulations! Go and use the security group as appropriate in Power BI!
Final Thoughts
This is a simple solution, but keep in mind this is a very specific solution for a very specific problem. I only use this solution for a very targeted set of groups. You essentially need to duplicate these groups and create a flow for each and every one of the groups you need to replicate. That can get messy really quickly. Personally, I would limit it to less than 10 groups, but you can decide what is right for you.
I would also consider putting this flow under a service account. If the person who sets up the flow uses their account, the flow will stop working when their account is disabled. A service account prevents any issues with this situation.
Have you run into this challenge? Do you think this solution works well? Tell me in the comments below!