SharePoint Online: Export Site Collection Administrators to a CSV Report
Requirement: Get Site Collection Administrators Report for SharePoint Online.
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:
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.
I’m looking for a similar script to fetch the administrator details for specific site URLs
This could help you: SharePoint Online: PowerShell to Get Site Collection Administrators
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..
Well, You must have site collection admin rights in order to retrieve existing site collection administrators. Add your account as Site collection administrator to all sites in the tenant first! How to Add Site Collection Admin to All SharePoint Online Sites?
Getting below error:
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
There’s an error in your script where you set the variable for $AdminURL but call out $AdminCenterURL. Thx.
Fixed it! Thanks PatrickD.