Sync members from O365 Modern group to a mail-enabled security group
I’ve seen a few scenarios where Office 365 modern groups were depended on for security access, but when trying to use them within Power BI you will find they are not available. Power BI really relies on mail enabled security groups that are not the O365 modern groups.
So, what do you do? There are probably other approaches that you may have come up with, and I’d love to hear about those in the comments. One workaround I came up with was to use PowerShell to create a mail enabled security group through Exchange Online and then match the group members from an existing Office 365 Modern group. Then you can reference the new mail enabled group, by email address, within Power BI. These can then be used within apps, organizational content packs, and more.
For the full script, head over to GitHub.
How the script works
This script will first create a new distribution group within Exchange Online if it doesn’t already exist.
## Update the managedby and PrimarySmtpAddress addresses
## Managed by = owner of group
## these can be changed later in the Exchange Online Admin portal
New-DistributionGroup -Name $newGroupName -Type "Security" -ManagedBy "asaxton@guyinacube.com" -PrimarySmtpAddress mygroup@guyinacube.com
After the new group is created, or if the group already exists, we will then get the members from both the old group (O365 Modern Group) and the new group (Mail-enabled security group).
$oldGroupMembers = Get-AzureADGroupMember -ObjectId $oldGroup.ObjectId -All $true
$newGroupMembers = Get-AzureADGroupMember -ObjectId $newGroup.ObjectId -All $true
Then we will loop through the old group members. First checking to see if the member is already in the group. If it isn’t, we add it. If it is, we just write a message indicating it already exists and move onto the next member.
## Add old members to new group
## Check to make sure the member doesn't already exist.
Foreach ($member in $oldGroupMembers)
{
if($newGroupMembers -notcontains $member)
{
Add-DistributionGroupMember -Identity $newGroupName -Member $member.UserPrincipalName
$message = "New group does not contain member – "
$message += $member.UserPrincipalName
Write-Output $message
}
else
{
$message = "New group contains member – "
$message += $member.UserPrincipalName
Write-Output $message
}
}
This can be re-run multiple times to make sure the Mail-enabled security group stays in sync with the O365 Modern group. So, if new users get added to the O365 Modern group, you can make sure they also get added to the Mail-enabled security group.
Limitations
A couple of things that are missing from the script that you add.
- Removal of users from the mail-enabled security group
- Adding/removing users from the Office 365 Modern Group
