Microsoft 365 – PowerShell script – Implementing SharePoint Governance – Fetching list of site collection administrators for all sites in tenant

Hi All,

Greetings !!!

Today sharing small but very important PowerShell script. Implementing SharePoint Governance.

Use Case :

• We have security assessment in our tenant.
• We found that for multiple site collections, there are more than two site collection administrators.
• Microsoft suggest best practice that – There must only two site collections administrators for given site collection
• Now we require a list of all sites, count of site collection administrators and list of site collection administrators – to achieve this we have written the PowerShell script

PowerShell script

• Script to traverse through all site collections in tenant, get all the site collection administrators and their count for respective site collection
• In case any error while processing any site collection, logging the error in error log file

Permission required to execute this script – Either one of the role is required

• Global Administrator
• SharePoint Administrator
• Respective administrator needs access to respective site collection

Detail Steps

• Here I am using my local development tenant
• Initialize following variables
  • #SharePoint admin site URL
    • $AdminCenterURL = “https://knowledgejunction1-admin.sharepoint.com”
  • #Path where we need to export the result
    • $ReportOutput=”C:\Prasham\Articles\PowerShell\knowledgejunction1_SiteCollectionAdmins.csv”
  • #Path of error log file
    • $ReportError=”C:\Prasham\Articles\PowerShell\knowledgejunction1_SiteCollectionAdmins_Errors.csv”

#Variables

#SharePoint admin site URL
$AdminCenterURL = "https://knowledgejunction1-admin.sharepoint.com"

#Path where we need to export the result
$ReportOutput="C:\Prasham\Articles\PowerShell\knowledgejunction1_SiteCollectionAdmins.csv"

#Path of error log file
$ReportError="C:\Prasham\Articles\PowerShell\knowledgejunction1_SiteCollectionAdmins_Errors.csv"

• Connect to SharePoint Online

#Connect to SharePoint Online - Credential popup will appear - enter the credentials
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)

• Get all site collections in the tenant

 #Get all Site colections - excluding onedrive sites
 $allTenantSites = Get-SPOSite -Limit ALL -IncludePersonalSite $false

• Declare variable – to prepare object to write in CSV file

#result array declaration
$ScaData = @()

• Loop through each site from all sites

Foreach ($Site in $allTenantSites)
        {
            try{
            }
        }

• Traverse through all site collections and get the site collection administrators

#Get all Site Collection Administrators with DisplayName and LoginName
 $SiteAdmins = Get-SPOUser -Site $Site.Url -Limit ALL | Where { $_.IsSiteAdmin -eq $True} | Select DisplayName, LoginName
 

• Prepare site collection admin object

#result array declaration for SCAs
$SiteCollectionAdmins = @()
      
$SiteAdmins | ForEach-Object {
      $SiteCollectionAdmins += ($_.DisplayName + " ("+ $_.LoginName +"); ")
}#$SiteAdmins | ForEach-Object

• Prepare object to write to CSV file

 #prepare object to write in CSV file
  $ScaData += New-Object PSObject -Property @{
             'Site Name' = $Site.Title
             'URL' = $Site.Url
             'Site Collection Admins Count' = $SiteCollectionAdmins.Count
             'Site Collection Admins' = $SiteCollectionAdmins -join ""
        }#$ScaData += New-Object PSObject -Property @

• Export the result to the CSV file

#Export the data to CSV
$ScaData | Export-Csv $ReportOutput -NoTypeInformation

Complete Script

#Variables

#SharePoint admin site URL
$AdminCenterURL = "https://knowledgejunction1-admin.sharepoint.com"

#Path where we need to export the result $ReportOutput="C:\Prasham\Articles\PowerShell\knowledgejunction1_SiteCollectionAdmins.csv"

#Path of error log file
$ReportError="C:\Prasham\Articles\PowerShell\knowledgejunction1_SiteCollectionAdmins_Errors.csv"
  
Try {
        #Connect to SharePoint Online
        Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)
  
        #Get all Site colections - excluding onedrive sites
        $allTenantSites = Get-SPOSite -Limit ALL -IncludePersonalSite $false
    
        #result array declaration
        $ScaData = @()
     
        #Get Site Collection Administrators of each site in tenant
        Foreach ($Site in $allTenantSites)
        {
            try{
                    Write-host -f Green "Processing Site Collection:" $Site.URL
      
                    #Get all Site Collection Administrators with DisplayName & LoginName
                    $SiteAdmins = Get-SPOUser -Site $Site.Url -Limit ALL | Where { 
                                $_.IsSiteAdmin -eq $True} | Select DisplayName, LoginName
 
                    #result arreay declaration for SCAs
                    $SiteCollectionAdmins = @()
                
                    $SiteAdmins | ForEach-Object {
                    $SiteCollectionAdmins += ($_.DisplayName + " ("+ $_.LoginName +"); ")

                    }#$SiteAdmins | ForEach-Object
                
                    #prepare object to write in CSV file
                    $ScaData += New-Object PSObject -Property @{
                        'Site Name' = $Site.Title
                        'URL' = $Site.Url
                         'Site Collection Admins Count' = $SiteCollectionAdmins.Count
                        'Site Collection Admins' = $SiteCollectionAdmins -join ""
                    }#$ScaData += New-Object PSObject -Property @

                }catch {
                          #in case any error - log to error file
                         write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
                            $exception_towrite =[pscustomobject]@{
                                'SiteURL' = $Site.Url
                                'Exception' = $($_.Exception.Message)
                            }
                            $exception_towrite | Export-Csv -Append -Path $ReportError
                        }#catch
        
        }#Foreach ($Site in $allTenantSites)

        #Export the data to CSV
        $ScaData | Export-Csv $ReportOutput -NoTypeInformation

        Write-Host -f Green "Site Collection Admninistrators Data Exported to CSV!"
    }
    catch {
            write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
        }

If we execute the script – two CSV files will be generated on the given location as

• CSV file having all the Site Collections in tenant and site collection administrator count and site collection administrators

• If there were some error while processing any site collection, there will be error log file as well as

Thanks for reading! HAVE A FANTASTIC TIME AHEAD!!!