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:

A site collection administrator has full control over a site collection, including the ability to manage permissions, content, and settings. Maintaining a list of site collection administrators in SharePoint Online can be important for security and governance reasons. 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 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
}

With this script, you can retrieve site collection administrators and generate a summarized report that includes all the site collection administrators in your SharePoint Online environment.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

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

  • In my case I got this error Error: Attempted to perform an unauthorized operation. :S is the same and I need to add my self to all sites as Site Collection Admin ?

    Reply
  • Hello Salaudeen. We have a big tenant with over 30k SPO sites. Is there any reason why the 2nd script on this page is taking over 20 minutes per site collection? At this rate it will never finish.

    Reply
  • 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. Required fields are marked *