Microsoft 365 – PowerShell script – Get all SharePoint sites where respective security group has permissions – using PnP PowerShell (connecting Tenant using Azure app client id and client secret key)

Microsoft 365 - PowerShell script - Get all SharePoint sites where respective security group has permissions - using PnP PowerShell (connecting Tenant using Azure app client id and client secret key)
Microsoft 365 - PowerShell script - Get all SharePoint sites where respective security group has permissions - using PnP PowerShell (connecting Tenant using Azure app client id and client secret key)

Hi All,

Greetings for the day 🙂 LIFE IS BEAUTIFUL 🙂

Today one more PowerShell script for Microsoft 365.

Details :

  • From one of our team I got request to find on which site collections in our tenant have permission to specific security group
  • Recently we have published article for PowerShell script to find the permission of SharePoint group on all site collection in respective tenant – Microsoft 365 / SharePoint online : PowerShell script – to get the list of SharePoint sites where given group has permission and export sites to CSV file
  • In this article we will discuss PowerShell script step by step to know the permission of Security group on all site collection from the tenant
  • Here, since we are connecting our M365 tenant using Azure app client id and client secret, we need to use PnP PowerShell CMDLET since SharePoint Online PowerShell doesn’t support connection M365 tenant with Azure App client id and Client secret

Detailed steps :

  • Connect to M365 tenant
#Connect to our M365 tenant - Please change here the teanant SharePoint site admin URL
Connect-PnPOnline -Url $AdminUrl -ClientId $ClientId -ClientSecret $ClientSecret

  • Once we successfully connected, we will get all site collections using Get-PnPTenantSite
#Get all SharePoint sites from our Tenant
$spoSites = Get-PnPTenantSite

  • We will traverse through all site collections and query to all users
  • Here please note, security group is stored as user in SharePoint
  • Security group is also added in “User Information List
  • So here we are querying against User, we are getting all users using – Get-PnPUser
  • Get-PnPUser – returns all the users including security group from current site collection
#verifying the groups of every site
foreach ($spoSite in $spoSites)
{
    try{
         Connect-PnPOnline -Url $spoSite.Url -ClientId $ClientId -ClientSecret $ClientSecret 
         $users  = Get-PnPUser | Where { $_.Title -like '*My Security Group Title*'}  

         # logging each group - though it each either 1 or 0 groups will be returned
         foreach ($group in $users) {
                $obj = New-Object Pscustomobject -Property @{
                       SiteURL = $spoSite.URL
                       GroupName = $group.Title
                }#object

                #generating object with data
                $Report += $obj
                
            }#foreach ($group in $groups)
        
   }catch{
        continue;
   }#catch   

 }#foreach ($spoSite in $spoSites)

  • Finally export our data to CSV file using Export-Csv
 #generate the CSV file
 $Report | Export-Csv $Path -NoTypeInformation

Complete Script :

<#
.SYNOPSIS
    Verify if specified security groups are in which SharePoint site collections from the tenant 
.DESCRIPTION
    Scan all SharePoint sites from the tenant.
    For Tenants with lots of sites this could take a long time, consider targeting at specific sites.
.PARAMETER AdminUrl
    Sharepoint Online tenant admin url
.PARAMETER ClientId
    App-only access using specified Azure ClientId
.PARAMETER ClientSecret
    App-only access using provided Client Secret for the specified ClientId
.EXAMPLE
    VerifyGroupAccess_AppId.ps1' -AdminUrl 'https://knowledgejunction1-admin.sharepoint.com' -ClientID 'xxxx' -ClientSecret 'xxxx'
.NOTES
    Author : Prasham Sabadra
    Version: 1.0
    The script works by retrieving users for each Modern Site/Classic Site Collection from the tenant
    Output is returned to CSV file. Currently CSV file path is hardcoded in script. Please change accordingly
.LINK
#>

param (

    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$AdminUrl,
    
    [Parameter(Mandatory=$false)]
    [ValidateNotNullOrEmpty()]
    [string]$ClientId ,
    
    [Parameter(Mandatory=$false)]
    [ValidateNotNullOrEmpty()]
    [string]$ClientSecret
    
)


#path where CSV file will be generated
$Path = 'c:\sitegroups.csv'

#Connect to our M365 tenant - Please change here the teanant SharePoint site admin URL
Connect-PnPOnline -Url $AdminUrl -ClientId $ClientId -ClientSecret $ClientSecret

#Get all SharePoint sites from our Tenant
$spoSites = Get-PnPTenantSite

$report = @()

#verifying the groups of every site
foreach ($spoSite in $spoSites)
{
    try{
         Connect-PnPOnline -Url $spoSite.Url -ClientId $ClientId -ClientSecret $ClientSecret
         $users  = Get-PnPUser | Where { $_.Title -like '*My Security Group Title*'}  


            # verifying each group - though it each either 1 or 0 groups will be returned 
            foreach ($group in $users)
            {
                $obj = New-Object Pscustomobject -Property @{
                SiteURL = $spoSite.URL
                GroupName = $group.Title
                }

                #generating object with data
                $Report += $obj                
            }#foreach ($group in $groups)
        
   }catch{
        continue;
   }#catch   
 }#foreach ($spoSite in $spoSites)
 
 #generate the CSV file
 $Report | Export-Csv $Path -NoTypeInformation

We have very good collection of Power Shell scripts, please have a look once – https://knowledge-junction.com/category/technology-articles/powershell-cmdlets/

Thanks for reading 🙂 STAY SAFE 🙂 STAY HEALTHY 🙂

Prasham Sabadra

LIFE IS VERY BEAUTIFUL :) ENJOY THE WHOLE JOURNEY :) Founder of Knowledge Junction and live-beautiful-life.com, Author, Learner, Passionate Techie, avid reader. Certified Professional Workshop Facilitator / Public Speaker. Scrum Foundation Professional certificated. Motivational, Behavioral , Technical speaker. Speaks in various events including SharePoint Saturdays, Boot camps, Collages / Schools, local chapter. Can reach me for Microsoft 365, Azure, DevOps, SharePoint, Teams, Power Platform, JavaScript.

You may also like...

1 Response

  1. October 4, 2021

    […] Microsoft 365 – PowerShell script – Get all SharePoint sites where respective security group has permissions – using PnP PowerShell (connecting Tenant using Azure app client id and client secret key) – https://knowledge-junction.com/2021/10/01/microsoft-365-powershell-script-get-all-sharepoint-sites-w&#8230; […]

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: