SharePoint Online: Export Site Collection Administrators to a CSV Report

Requirement: Get Site Collection Administrators Report for SharePoint Online.

You must have site collection Admin rights, in order to get existing site collection administrators of a SharePoint Online site through PowerShell! How to Add Site Collection Admin to All SharePoint Online Sites?

PowerShell to Get Site Collection Administrators in SharePoint Online and Export to CSV:

If you’re a SharePoint Online administrator, there may be a time when you need to export the site collection administrators from all sites in your environment to a CSV file. E.g., you may need this to document who has access and what sites, or if you need to have a record of site administrators for auditing purposes. This article shows you how to use PowerShell to extract this information and export site collection administrators to a CSV file.

Let’s get site collection admins of all SharePoint Online sites and export them to a CSV report using PowerShell.

#Variables for processing
$AdminCenterURL = "https://Crescent-admin.sharepoint.com"
$ReportOutput="C:\Temp\SiteCollectionAdmins.csv"
 
Try {
    #Connect to SharePoint Online
    Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)
 
    #Get all Site colections
    $Sites = Get-SPOSite -Limit ALL
    $SiteData = @()
    
    #Get Site Collection Administrators of Each site
    Foreach ($Site in $Sites)
    {
        Write-host -f Yellow "Processing Site Collection:"$Site.URL
     
        #Get all Site Collection Administrators
        $SiteAdmins = Get-SPOUser -Site $Site.Url -Limit ALL | Where { $_.IsSiteAdmin -eq $True} | Select DisplayName, LoginName

        #Get Site Collection Details
        $SiteAdmins | ForEach-Object {
        $SiteData += New-Object PSObject -Property @{
                'Site Name' = $Site.Title
                'URL' = $Site.Url
                'Site Collection Admins' = $_.DisplayName + " ("+ $_.LoginName +"); "
                }
        }
    }
    $SiteData 
    #Export the data to CSV
    $SiteData | Export-Csv $ReportOutput -NoTypeInformation
    Write-Host -f Green "Site Collection Admninistrators Data Exported to CSV!"
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

And the site collection administrators CSV report for all SharePoint Online site collections in the tenant:

sharepoint online get site collection administrators and export to CSV

Wait, There is one problem! On group-connected sites, You’ll see just the “Group Owners” in the above script outputs (E.g, “Marketing Owners”). Here is how to expand each group to get the Owners of the group and export them to a CSV report using PnP PowerShell.

#Set Variables
$AdminSiteURL = "https://crescent-admin.sharepoint.com"
$ReportOutput = "C:\Temp\SiteAdmins.csv"

Try {
    #Connect to Admin Center
    Connect-PnPOnline -Url $AdminSiteURL -Interactive
  
    #Get All Site collections - Exclude: Seach Center, Mysite Host, App Catalog, Redirect, Content Type Hub, eDiscovery and Bot Sites
    $SiteCollections = Get-PnPTenantSite | Where -Property Template -NotIn ("SRCHCEN#0", "REDIRECTSITE#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")
 
    #Loop through each site collection
    $SiteAdminData = @()
    ForEach($Site in $SiteCollections) 
    { 
        Write-host -F Yellow "Getting Site Collection Administrators of the site: " $Site.Url 
        Connect-PnPOnline -Url $Site.Url -Interactive
    
        #Get Site Collection Administrators
        $SiteAdminstrators = @()
        Get-PnPSiteCollectionAdmin -PipelineVariable Admin | ForEach-Object {
            If($_.PrincipalType -eq "SecurityGroup") #Check if its a Microsoft 365 group
            {
                #Get Members of the Group
                $Group = Get-PnPMicrosoft365Group -IncludeOwners | Where {$_.Mail -eq $Admin.Email}
                $Group.Owners | Select Email | ForEach-Object {
                    $SiteAdminstrators += $_.Email
                }
            }
            Else
            {
                $SiteAdminstrators += $Admin.Email
            }
        }
        $Adminstrators = ($SiteAdminstrators | select -Unique) -join ";"

        #Collection Site Admin Details
        $SiteAdminData += New-Object PSObject -Property @{
            'Site Name' = $Site.Title
            'URL' = $Site.Url
            'Site Collection Admins' = $Adminstrators
        }
    }

    $SiteAdminData

    #Export the data to CSV
    $SiteAdminData | Export-Csv $ReportOutput -NoTypeInformation
    Write-Host -f Green "Site Collection Admninistrators Data Exported to CSV!"
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Get Site Collection Administrators of All OneDrive for Business Sites

Here is the PnP PowerShell to get all OneDrive for Business sites.

#Variables for processing
$AdminCenterURL = "https://crescent-admin.sharepoint.com"
$ReportOutput="C:\Temp\OneDriveAdmins.csv"

Try {
    #Connect to SharePoint Online Admin Center
    Connect-PnPOnline -url $AdminCenterURL -Interactive
  
    #Get all OneDrive Site colections
    $OneDriveSites = Get-PnPTenantSite -IncludeOneDriveSites -Filter "Url -like '-my.sharepoint.com/personal/'"
    $SiteAdminData = @()
     
    #Get Site Collection Administrators of Each site
    Foreach ($Site in $OneDriveSites)
    {
        Write-host -f Yellow "Processing Site Collection:"$Site.URL
        Connect-PnPOnline -url $Site.URL -Interactive

        #Get all Site Collection Administrators
        $SiteAdmins = Get-PnPSiteCollectionAdmin
 
        #Get Site Collection Details
        $SiteAdminData += New-Object PSObject -Property ([ordered]@{
                'Site Name' = $Site.Title
                'URL' = $Site.Url
                'Site Collection Admins' = ($SiteAdmins | Select -ExpandProperty Email) -join ";"
                })
    }
    $SiteAdminData
    #Export the data to CSV
    $SiteAdminData | Export-Csv $ReportOutput -NoTypeInformation
    Write-Host -f Green "Site Collection Admninistrators Data Exported to CSV!"
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

7 thoughts on “SharePoint Online: Export Site Collection Administrators to a CSV Report

  • I’m looking for a similar script to fetch the administrator details for specific site URLs

    Reply
  • Hello Salaudeen,

    i cant seem to get the script to work, I get the same access denied error after it parses through a couple of sites.

    Processing Site Collection: https://Crescenttech-admin.sharepoint.com/
    Processing Site Collection: https://Crescenttech-admin.sharepoint.com/
    Processing Site Collection: https://Crescenttech-admin.sharepoint.com/
    Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

    i have removed the site name from my post..

    Reply
  • Getting below error:

    Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

    Reply
  • There’s an error in your script where you set the variable for $AdminURL but call out $AdminCenterURL. Thx.

    Reply

Leave a Reply

Your email address will not be published.