Power BI Group management using Active Directory roles and PowerShell

[Anonymous]

Office 365 Groups are an excellent way of scoping and securing Power BI content for end users. In addition to a content and security container the Group construct offers the best of Office 365 with hooks into OneDrive for Business, Outlook, Planner, SharePoint, Stream and Yammer.

Manually maintaining Power BI Group membership on a small scale may make sense but for large-scale or role-based access, manual Group maintenance is not an option. For the later situation extending Active Directory (AD) roles to Power BI is the right step forward. Unfortunately, using AD roles as the basis for Power BI Group membership lacks automatic maintenance and as such AD role additions and removals do not cascade to O365.

The “Users and groups cmdlets in Exchange Online” PowerShell commands can be used to maintain AD role membership. The script, appended below, stores the membership of both the AD role and O365 Group; compares them and then performs the required add/remove operations.

Set-ExecutionPolicy Unrestricted -Scope CurrentUser -Force
 
# Get security credential based on a user name and password
$User_Credential = Get-Credential
 
# Get Exchange cmdlets
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $User_Credential -Authentication Basic -AllowRedirection
Import-PSSession $Session -Allowclobber
 
# Store Role and Group names; change as required
$AD_Role_Name = "AD Role name here"
$O365_Group_Name = "O365 Group name here"
 
# Get AD role membership
$Active_Directory_Role = get-group -Identity $AD_Role_Name | Select-Object -Property Members | foreach { $_.Members } | Sort-Object
 
# Get O365 Group membership
$Office_365_Group = get-group -Identity $O365_Group_Name | Select-Object -Property Members | foreach { $_.Members } | Sort-Object
 
# Find and store new AD role users
$Add_O365_Group = Compare-Object $Active_Directory_Role $Office_365_Group -PassThru | Left_Side
 
# Add new AD role users
$Add_O365_Group | % {Add-UnifiedGroupLinks -Identity $O365_Group_Name -LinkType Members -links "$_"}
 
# Debug print users
Write-Host "Added the following users: " $Add_O365_Group -foregroundcolor black -backgroundColor Green
 
# Find and store old AD role users; switch filter position of AD_Role and O365_Group
$Remove_O365_Group = Compare-Object $Office_365_Group $Active_Directory_Role -PassThru | Left_Side
 
# Remove old AD role users
$Remove_O365_Group | % {Remove-UnifiedGroupLinks -Identity $O365_Group_Name -LinkType Members -links "$_"}
 
# Debug print users
Write-Host "Removed the following users: " $Remove_O365_Group -foregroundcolor black -backgroundColor Yellow

These commands require elevated permissions within the O365 tenant and with some tweaking the routine could be scheduled along with other Active Directory sync operations.

Thanks for stopping by.

NY