Get All Site Administrators and Owners With Full Control – SharePoint Online PowerShell

A commonly posed question within an organisation that use SharePoint Online is "Can I get a list of all the site administrators?". A perfectly reasonable question, I am sure you will agree. Regardless of whether your role is a SharePoint Administrator, Office 365 Administrator or even a PowerShell Developer, you will spot there is more to this request than meets the eye. You will no doubt start googling looking for scripts that get all SharePoint Administrators or all SharePoint Site Owners. Why re-invent the wheel after all.

Google SharePoint Administrators PowerShell

I found several scripts and blog posts that mostly met my needs. I did feel the need to start from the ground up so this is what I am sharing with you in this post.

Site Administrators or Members with Full Control?

Most administrators will find themselves looking initially at Get-PnPSiteCollectionAdmin, which is an admirable starting point.

Your requestor may have no interest in those users who inherit Full Control from a group. If so, it may well be time to pack up and go home. But there are a few other things to consider in order to provide a detailed permission audit. There are users who have owner rights and can make changes to SharePoint Online that aren't administrators. From my experience, what the person is normally requesting, is for a list of all the people who can make changes. Sometimes they look for those who have ownership of a site or site collection as they can actually make changes or delete things.

You may even find yourself doing something like this:

$Cred = Get-Credential
Connect-PnPOnline -Url $Site.Url -Credentials $Cred

#Get All Site Collections 
$Sites = Get-PnPTenantSite

Disconnect-PnPOnline

#Loop through each Site Collection
ForEach ($Site in $Sites) {

    Write-host "Processing data for $Site.Url" 
    Connect-PnPOnline -Url $Site.Url -Credentials $Cred
    #Get Site Collection Administrators
    Get-PnPSiteCollectionAdmin

    Disconnect-PnPOnline
}

NB: I always opt to use the SharePointPnPPowerShellOnline module over the "SharePoint Online Management Shell" as PnP handles unattended authentication and is most cases is the best approach nowadays.

All Administrators and Users With Full Control, But Why?

Here are some of the things I have found myself considering when looking to run an audit of this nature:

  • When using Get-PnPTenantSite, do we really want to be auditing Search Center, Mysite (OneDrive), App Catalog etc. Can these be omitted?
  • Administrators, normally the IT folks, are added as administrators at the SPO admin portal. Not all of the owners.
  • Site Collection owners can add other owners, normally by going to "People and Groups" within a site.
  • These users could be added to a group in order to inherit permissions or could be added directly, not within a group and granted full control.
  • The groups themselves. There is a default "<Site Name> Owners" group with Full Control, but others may have been created with other names that don't include the word "owner".
  • Sites, Subsites, Sub-Subsites etc. These can all have their own permission model. A method to locate these child elements and their permissions could be called for.
  • Permissions the authenticated administrator has, or does not have, may restrict the results providing some "Access Denied" errors when targeting all sites and subsites.

What is The Script's Goal

My goal with this script is to meet some the considerations above. So let's target all sites, subsites and sub-subsites etc. We will look to capture all administrators and all members who have full control. Then we'll record whether the site is a parent or child element and if the user inherits permissions from a group, I will name the group.

Before We Run Any Scripts

In order to avoid any permission errors and to best utilise modern authentication methods, I always opt to make use of a Service Principal when globally auditing SharePoint Online, OneDrive and other Office 365 & Azure platforms. To make use of the upcoming script, please see the pre-requisite Connect-PnPOnline Unattended Using Azure App-Only Tokens. This will help you create a Service Principal that will work seamlessly with the Export-SPOAdmin.

You will see as we move on to describe the actions within Export-SPOAdmin why the above will be important and how we prompt to capture the required values.

Export-SPOAdmin – Breaking The Script Down to Expose The Function

The script is made up of 4 advanced functions and 2 functions encased within New-Module. There is only 1 function exposed using Export-ModuleMember after loading the script and that is Export-SPOAdmin.

PowerShell Export-SPOAdmin

Here is a breakdown of the 6 functions:

  • Invoke-Prerequisites: Set's the date format to suit the file export in eith GB or US Format. dd-MM-yy or MM-dd-yy.
  • Get-Administrators: Utilised Get-PnPSiteCollectionAdmin to get the administrators set at the admin portal. Probably those IT folks I mentioned in the opening paragraph.
  • Get-OwnerNoGroup: Looks for those users, not groups who have a RoleBinding containing Full Control.
  • Get-OwnerFromGroup: Looks for groups with RoleTypeKind that equals "Administrator". more easy to handle.
  • Export-SPOAdmin: What brings it all together. This advanced function loops through all sites, subsites, sub-subsites etc and calls each worker function as it goes to populate the data variable. Authentication, Connect-PnPOnline then Disconnect-PnPOnline is called in sequence in order to make sure the correct resource is connected to at any given time.

Are we ready for the complete script?

New-Module {

    Function Invoke-Prerequisites {

        [OutputType()]
        [CmdletBinding()]
        Param (
            [Parameter(Position = 1)]
            [string] $Tenant,
            [Parameter(Position = 2)]
            [string] $ClientID,
            [Parameter(Position = 3)]
            [string] $CertPath,
            [Parameter(Position = 4)]
            [string] $CertPass
        )

        $Script:Stopwatch = [system.diagnostics.stopwatch]::StartNew()

        If ((Get-Culture).LCID -eq "1033") {

            $Script:Date = (Get-Date).tostring("MM-dd-yy")

        }
        Else {

            $Script:Date = (Get-Date).tostring("dd-MM-yy")

        }

        $Script:Tenant = $Tenant

        $Script:TenantUrl = "https://$($Tenant).sharepoint.com"
        $Script:AadDomain = "$($Tenant).onmicrosoft.com"
        $Script:ClientID = $ClientID
        $Script:CertPass = $CertPass
        $Script:CertPath = $CertPath

    }

    Function Get-Administrators {

        $Admins = Get-PnPSiteCollectionAdmin

        <# Below gets users who have full control - set as administrator via admin portal #>
        ForEach ($Admin in $Admins | Where-Object { $_ -ne "System Account" }) {

            $Datum = New-Object -TypeName PSObject

            $Datum | Add-Member -MemberType NoteProperty -Name Tenant -Value $Tenant
            $Datum | Add-Member -MemberType NoteProperty -Name Site -Value $SiteUrl
            $Datum | Add-Member -MemberType NoteProperty -Name Group -Value "Aministrators"

            Switch ($Admin.PrincipalType) {

                "User" { 
                    $Datum | Add-Member -MemberType NoteProperty -Name Member -Value $Admin.Title 
                }
                "SecurityGroup" {
                    If ($Admin.Title -ne "Company Administrator") {
                        $Datum | Add-Member -MemberType NoteProperty -Name Member -Value "$($Admin.Title) - AD Group" 
                    }
                    ElseIf ($Admin.Title -eq "SharePoint Service Administrator") {
                        $Datum | Add-Member -MemberType NoteProperty -Name Member -Value $($Admin.Title)
                    }
                    Else {
                        $Datum | Add-Member -MemberType NoteProperty -Name Member -Value "Global Admins" 
                    }
                }

            }

            $Datum | Add-Member -MemberType NoteProperty -Name Subsite -Value "No"
            $Datum | Add-Member -MemberType NoteProperty -Name Permissions -Value "Unique"

            $Script:Data += $Datum

        }

    }

    Function Get-OwnerNoGroup {

        Param(
            [Parameter(Mandatory = $true, Position = 0)]
            [string]$Subsite
        )

        $Web = Get-PnPWeb -Includes RoleAssignments

        ForEach ($RA in $Web.RoleAssignments) {

            $RoleBindings = Get-PnPProperty -ClientObject $RA -Property RoleDefinitionBindings
            $PrincipalType = Get-PnPProperty -ClientObject $($RA.Member) -Property PrincipalType

            $RoleTypeKind = ($RoleBindings.RoleTypeKind).ToString()
            $PType = $PrincipalType.ToString()

            If ($PType -eq "User" -and $RoleTypeKind -eq "Administrator") {

                $Title = Get-PnPProperty -ClientObject $($RA.Member) -Property Title

                $Datum = New-Object -TypeName PSObject

                $Datum | Add-Member -MemberType NoteProperty -Name Tenant -Value $Tenant
                If ($Subsite -eq "No") { 
                    $Datum | Add-Member -MemberType NoteProperty -Name Site -Value $SiteUrl 
                } 
                Else { 
                    $Datum | Add-Member -MemberType NoteProperty -Name Site -Value $SubsiteUrl 
                }
                $Datum | Add-Member -MemberType NoteProperty -Name Group -Value "N/A"
                $Datum | Add-Member -MemberType NoteProperty -Name Member -Value $Title
                $Datum | Add-Member -MemberType NoteProperty -Name Subsite -Value $Subsite
                $Datum | Add-Member -MemberType NoteProperty -Name Permissions -Value "Unique"

                $Script:Data += $Datum

            }

        }

    }

    Function Get-OwnerFromGroup {

        Param(
            [Parameter(Mandatory = $true, Position = 0)]
            [string]$Subsite,
            [Parameter(Position = 1)]
            [string]$SiteUrl,
            [Parameter(Position = 1)]
            [string]$SubsiteUrl
        )

        # HasUniqueRoleAssignments
        $Groups = Get-PnPGroup | 
            Where-Object { $_.Title -notlike "SharingLinks.*" -and $_.Title -notlike "Limited Access*" } | 
            Select-Object Title, Users, PrincipalType, Id

        If ($Subsite -eq "No") { 
            Write-Host "Auditing: $($SiteUrl)" -ForegroundColor Cyan
        } 
        Else { 
            Write-Host "Auditing: $($SubsiteUrl)" -ForegroundColor Cyan
        }

        ForEach ($Group in $Groups | Where-Object { $_.PrincipalType -eq "SharePointGroup" }) {

            $GroupPermission = Get-PnPGroupPermissions -Identity $Group.Title -ErrorAction SilentlyContinue | 
                Where-Object { $_.Hidden -like "False" } 

            If ($GroupPermission.RoleTypeKind -eq "Administrator") {
                
                ForEach ($G in $Group | Where-Object { $_.Users.Title -ne "System Account" }) {

                    $Members = Get-PnPGroupMembers -Identity $G.Id | Select-Object LoginName, Title, PrincipalType
                    $Members = $Members | Where-Object { $_.Title -ne "System Account" -and $_.PrincipalType -eq "User" } 

                    If ($Members) {

                        ForEach ($Member in $Members) {

                            If ($Site.HasUniqueRoleAssignments -eq $null -or $Site.HasUniqueRoleAssignments -eq $true) { 
                                $InheritsPermissions = "Unique" 
                            }
                            Else { 
                                $InheritsPermissions = "Inherited"
                            }

                            $Datum = New-Object -TypeName PSObject

                            $Datum | Add-Member -MemberType NoteProperty -Name Tenant -Value $Tenant
                            If ($Subsite -eq "No") { 
                                $Datum | Add-Member -MemberType NoteProperty -Name Site -Value $SiteUrl 
                            } 
                            Else { 
                                $Datum | Add-Member -MemberType NoteProperty -Name Site -Value $SubsiteUrl 
                            }
                            $Datum | Add-Member -MemberType NoteProperty -Name Group -Value $Group.Title
                            $Datum | Add-Member -MemberType NoteProperty -Name Member -Value $Member.Title
                            $Datum | Add-Member -MemberType NoteProperty -Name Subsite -Value $Subsite
                            $Datum | Add-Member -MemberType NoteProperty -Name Permissions -Value $InheritsPermissions

                            $Script:Data += $Datum

                        }

                    }

                }

            }

        }

    }

    Function Invoke-FilePicker {

        Write-Host "Select your certificate .pfx file"

        Add-Type -AssemblyName System.Windows.Forms

        $Dialog = New-Object System.Windows.Forms.OpenFileDialog
        $Dialog.InitialDirectory = "$InitialDirectory"
        $Dialog.Title = "Select your certificate .pfx file"
        $Dialog.Filter = "Certificate file|*.pfx"  
        $Dialog.Multiselect = $false
        $Result = $Dialog.ShowDialog()

        If ($Result -eq 'OK') {

            Try {

                $Script:CertPath = $Dialog.FileNames
            }

            Catch {

                $Script:CertPath = $null
                Break
            }
        }

        Else {

            Write-Host "Notice: No file selected." -ForegroundColor Yellow
            Break

        }
        
    }

    Function Export-SPOAdmin {

        [OutputType()]
        [CmdletBinding()]
        Param (
            [Parameter(
                Mandatory = $true, 
                Position = 1, 
                HelpMessage = "Enter your O365 tenant name, like 'contoso'"
            )]
            [ValidateNotNullorEmpty()]
            [string] $Tenant,
            [Parameter(
                Mandatory = $true, 
                Position = 2, 
                HelpMessage = "Enter your Az App Client ID"
            )]
            [ValidateNotNullorEmpty()]
            [string] $ClientID
        )

        #ToDo: Exclude SubSites Switch

        BEGIN {

            Invoke-FilePicker

            $Script:CertPass = Read-Host "Enter your certificate password"

            $Params = @{
                Tenant   = $Tenant
                ClientID = $ClientID
                CertPath = "$CertPath"
                CertPass = $CertPass
            }

            Invoke-Prerequisites @Params

            $Params = @{
                ClientId            = $ClientID
                CertificatePath     = $CertPath
                CertificatePassword = (ConvertTo-SecureString -AsPlainText $CertPass -Force)
                Url                 = $TenantUrl
                Tenant              = $AadDomain
            }

        }
        PROCESS {

            Connect-PnPOnline @Params -WarningAction SilentlyContinue
            
            $Script:Sites = Get-PnPTenantSite -Filter "Url -notlike '*/portals/*'" | Where-Object -Property Template -NotIn (
                "SRCHCEN#0", 
                "SPSMSITEHOST#0", 
                "APPCATALOG#0", 
                "POINTPUBLISHINGHUB#0", 
                "EDISC#0", 
                "STS#-1"
            )

            $Sites = $Sites.Url

            <# For Testing: Add - "$Sites = $Sites | Select -First 5 -Skip 5" or similar below this comment #>

            Disconnect-PnPOnline

            $Script:Data = @()

            ForEach ($SiteUrl in $Sites) {

                $Subsite = "No"

                $Params = @{
                    ClientId            = $ClientID
                    CertificatePath     = $CertPath
                    CertificatePassword = (ConvertTo-SecureString -AsPlainText $CertPass -Force)
                    Url                 = $SiteUrl
                    Tenant              = $AadDomain
                }

                Connect-PnPOnline @Params -WarningAction SilentlyContinue

                <# Below gets users who have full control - directly applied at the root #>
                Get-OwnerNoGroup -Subsite $Subsite

                <# Below gets users who have full control - inherited from a group #>
                Get-OwnerFromGroup -Subsite $Subsite -SiteUrl $SiteUrl

                <# Below gets users who have full control - set as administrator via admin portal #>
                Get-Administrators

                $SubSites = Get-PnpSubwebs -Recurse -Includes HasUniqueRoleAssignments # need to do more with this
                # $SubSites = Get-PnPSubWebs -Recurse

                Disconnect-PnPOnline

                If ($SubSites) {

                    ForEach ($Site in $SubSites) {

                        $Subsite = "Yes"
                        $SubsiteUrl = $Site.Url

                        $Params = @{
                            ClientId            = $ClientID
                            CertificatePath     = $CertPath
                            CertificatePassword = (ConvertTo-SecureString -AsPlainText $CertPass -Force)
                            Url                 = $SubsiteUrl
                            Tenant              = $AadDomain
                        }

                        Connect-PnPOnline @Params -WarningAction SilentlyContinue

                        <# Below gets users who have full control - directly applied at the root #>
                        Get-OwnerNoGroup -Subsite $Subsite

                        <# Below gets users who have full control - inherited from a group #>
                        Get-OwnerFromGroup -Subsite $Subsite -SubsiteUrl $SubsiteUrl

                        Write-Host "Subsite processed: " -ForegroundColor White -NoNewline
                        Write-Host "$($SubsiteUrl)" -ForegroundColor DarkGreen

                        Disconnect-PnPOnline

                    }

                }

                Write-Host "Site processed: " -ForegroundColor White -NoNewline
                Write-Host "$($SiteUrl)" -ForegroundColor Green

            }

        }
        END {
            
            If ($Data) {

                $Path = ".\"
                $FileName = "$Tenant-SPOAdmins-$Date.csv"
                $Data | Export-Csv -Path "$Path$FileName" -NoTypeInformation
                $Location = Get-Location

                Write-Host
                Write-Host "File called " -NoNewline
                Write-Host "'$FileName' " -ForegroundColor Green -NoNewline
                Write-Host "exported to " -NoNewline
                Write-Host "$Location" -ForegroundColor Green
                Write-Host
                
            }
            Else {

                Write-Host "No Data to Export"

            }

            $TotalSecs = [math]::Round($StopWatch.Elapsed.TotalSeconds, 0)
            $StopWatch.Stop()

            If ($TotalSecs -lt 60) { 

                Write-Host "Job Took " -NoNewline
                Write-Host "$TotalSecs Seconds " -NoNewline -ForegroundColor Cyan
                Write-Host "to Complete"


            }
            ElseIf ($TotalSecs -ge 60 -and $TotalSecs -lt 3600) {

                $Count = New-TimeSpan -Seconds $TotalSecs

                Write-Host "Job Took " -NoNewline
                Write-Host "$($Count.Minutes) Minutes " -NoNewline -ForegroundColor Cyan
                Write-Host "and $($Count.Seconds) Seconds " -NoNewline -ForegroundColor Cyan
                Write-Host "to Complete"
            }
            Else {

                $Count = New-TimeSpan -Seconds $TotalSecs

                Write-Host "Job Took " -NoNewline
                Write-Host "$($Count.Hours) Hours, " -NoNewline -ForegroundColor Cyan
                Write-Host "$($Count.Minutes) Minutes and " -NoNewline -ForegroundColor Cyan
                Write-Host "$($Count.Seconds) Seconds " -NoNewline -ForegroundColor Cyan
                Write-Host "to Complete"

            }

        }

    }

    Export-ModuleMember Export-SPOAdmin

} | Out-Null

You can of course visit my Github at https://github.com/AlanPS1 to get the code from there too.

Running the script

In order to expose the function or cmdlet called Export-SPOAdmin you will first have to load the script using one of these 2 methods.

Import-Module .\Export-SPOAdmin.ps1 
. .\Export-SPOAdmin.ps1 

Once you have done either of the above at a location that contains the script, you will now be able to run the function.

There are 2 mandatory parameters that can be passed to the function or you will be prompted for. See below:

Export-SPOAdmin -Tenant "contoso" -ClientID "1r9g91f2-0e9f-45bc-9c66-213h6938598f"
PowerShell.exe

After successful entry of the Tenant and ClientID, you will automatically be prompted to select the location of your .pfx file.

Using the Function

Once you have selected the pfx file, you will be asked to enter your certificate password. This will validate the values you have entered. Based on a successful validation of your parameters, the script will run. It will in turn audit your SharePoint Online Administrators and those owners who have Full Control.

Export-SPOAdmin Results

As you will see from the last line of the output above, the script will tell you the name of your file and the location of it once it has exported.

Locate the file and you will see the results.

Export-SPOAdmin excel output

I hope this can be helpful to others.

Thanks for reading, Alan.