SharePoint Online User Permission Report

Nguyen, Tee 106 Reputation points
2023-03-08T05:41:15.4733333+00:00

Hi, we have a need to run SharePoint online user access report and requirement as follow:

  1. We have a list of 500 users with email or UPN from CSV file
  2. We need to check each of these 500 users to see which SPO site he or she belong to & permission including SharePoint or Teamsite and this report will be generated and output to CSV file

Are there any existing PowerShell to allow us to run above report?

Thanks

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,560 questions
0 comments No comments
{count} votes

Accepted answer
  1. RaytheonXie_MSFT 30,906 Reputation points Microsoft Vendor
    2023-03-08T08:34:48.6766667+00:00

    Hi @Nguyen, Tee

    Per my research, there is no such function to retrieve sharepoint site by users permission. We can only get permission report of sitecollections. Please refer to the following powershell script

    #sharepoint online powershell permissions report
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" 
      
    #Function to Get Permissions Applied on a particular Object, such as: Web, List or Item
    Function Get-Permissions([Microsoft.SharePoint.Client.SecurableObject]$Object)
    {
        #Determine the type of the object
        Switch($Object.TypedObject.ToString())
        {
            "Microsoft.SharePoint.Client.Web"  { $ObjectType = "Site" ; $ObjectURL = $Object.URL }
            "Microsoft.SharePoint.Client.ListItem"
            {
                $ObjectType = "List Item"
                #Get the URL of the List Item
                $Object.ParentList.Retrieve("DefaultDisplayFormUrl")
                $Ctx.ExecuteQuery()
                $DefaultDisplayFormUrl = $Object.ParentList.DefaultDisplayFormUrl
                $ObjectURL = $("{0}{1}?ID={2}" -f $Ctx.Web.Url.Replace($Ctx.Web.ServerRelativeUrl,''), $DefaultDisplayFormUrl,$Object.ID)
            }
            Default
            {
                $ObjectType = "List/Library"
                #Get the URL of the List or Library
                $Ctx.Load($Object.RootFolder)
                $Ctx.ExecuteQuery()           
                $ObjectURL = $("{0}{1}" -f $Ctx.Web.Url.Replace($Ctx.Web.ServerRelativeUrl,''), $Object.RootFolder.ServerRelativeUrl)
            }
        }
      
        #Get permissions assigned to the object
        $Ctx.Load($Object.RoleAssignments)
        $Ctx.ExecuteQuery()
      
        Foreach($RoleAssignment in $Object.RoleAssignments)
        {
                    $Ctx.Load($RoleAssignment.Member)
                    $Ctx.executeQuery()
                      
                    #Get the Permissions on the given object
                    $Permissions=@()
                    $Ctx.Load($RoleAssignment.RoleDefinitionBindings)
                    $Ctx.ExecuteQuery()
                    Foreach ($RoleDefinition in $RoleAssignment.RoleDefinitionBindings)
                    {
                        $Permissions += $RoleDefinition.Name +";"
                    }
      
                    #Check direct permissions
                    if($RoleAssignment.Member.PrincipalType -eq "User")
                    {
                            #Send the Data to Report file
                            "$($ObjectURL) `t $($ObjectType) `t $($Object.Title)`t $($RoleAssignment.Member.LoginName) `t User `t $($Permissions)" | Out-File $ReportFile -Append
                    }
                      
                    ElseIf($RoleAssignment.Member.PrincipalType -eq "SharePointGroup")
                    {       
                            #Send the Data to Report file
                            "$($ObjectURL) `t $($ObjectType) `t $($Object.Title)`t $($RoleAssignment.Member.LoginName) `t SharePoint Group `t $($Permissions)" | Out-File $ReportFile -Append
                    }
                    ElseIf($RoleAssignment.Member.PrincipalType -eq "SecurityGroup")
                    {
                        #Send the Data to Report file
                        "$($ObjectURL) `t $($ObjectType) `t $($Object.Title)`t $($RoleAssignment.Member.Title)`t $($Permissions) `t Security Group" | Out-File $ReportFile -Append
                    }
        }
    }
      
    #powershell to get sharepoint online site permissions
    Function Generate-SPOSitePermissionRpt($SiteURL,$ReportFile)
    {
        Try {
            #Get Credentials to connect
            $Cred= Get-Credential
            $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
       
            #Setup the context
            $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
            $Ctx.Credentials = $Credentials
      
            #Get the Web
            $Web = $Ctx.Web
            $Ctx.Load($Web)
            $Ctx.ExecuteQuery()
      
            #Write CSV- TAB Separated File) Header
            "URL `t Object `t Title `t Account `t PermissionType `t Permissions" | out-file $ReportFile
      
            Write-host -f Yellow "Getting Site Collection Administrators..."
            #Get Site Collection Administrators
            $SiteUsers= $Ctx.Web.SiteUsers
            $Ctx.Load($SiteUsers)
            $Ctx.ExecuteQuery()
            $SiteAdmins = $SiteUsers | Where { $_.IsSiteAdmin -eq $true}
      
            ForEach($Admin in $SiteAdmins)
            {
                #Send the Data to report file
                "$($Web.URL) `t Site Collection `t $($Web.Title)`t $($Admin.Title) `t Site Collection Administrator `t  Site Collection Administrator" | Out-File $ReportFile -Append
            }
      
            #Function to Get Permissions of All List Items of a given List
            Function Get-SPOListItemsPermission([Microsoft.SharePoint.Client.List]$List)
            {
                Write-host -f Yellow "`t `t Getting Permissions of List Items in the List:"$List.Title
     
                $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
                $Query.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>$BatchSize</RowLimit></View>"
     
                $Counter = 0
                #Batch process list items - to mitigate list threshold issue on larger lists
                Do { 
                    #Get items from the list
                    $ListItems = $List.GetItems($Query)
                    $Ctx.Load($ListItems)
                    $Ctx.ExecuteQuery()
               
                    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
      
                    #Loop through each List item
                    ForEach($ListItem in $ListItems)
                    {
                        $ListItem.Retrieve("HasUniqueRoleAssignments")
                        $Ctx.ExecuteQuery()
                        If($ListItem.HasUniqueRoleAssignments -eq $True)
                        {
                            #Call the function to generate Permission report
                            Get-Permissions -Object $ListItem
                        }
                        $Counter++
                        Write-Progress -PercentComplete ($Counter / ($List.ItemCount) * 100) -Activity "Processing Items $Counter of $($List.ItemCount)" -Status "Searching Unique Permissions in List Items of '$($List.Title)'"
                    }
                } While ($Query.ListItemCollectionPosition -ne $null)
            }
      
            #Function to Get Permissions of all lists from the web
            Function Get-SPOListPermission([Microsoft.SharePoint.Client.Web]$Web)
            {
                #Get All Lists from the web
                $Lists = $Web.Lists
                $Ctx.Load($Lists)
                $Ctx.ExecuteQuery()
      
                #Get all lists from the web  
                ForEach($List in $Lists)
                {
                    #Exclude System Lists
                    If($List.Hidden -eq $False)
                    {
                        #Get List Items Permissions
                        Get-SPOListItemsPermission $List
      
                        #Get the Lists with Unique permission
                        $List.Retrieve("HasUniqueRoleAssignments")
                        $Ctx.ExecuteQuery()
      
                        If( $List.HasUniqueRoleAssignments -eq $True)
                        {
                            #Call the function to check permissions
                            Get-Permissions -Object $List
                        }
                    }
                }
            }
      
            #Function to Get Webs's Permissions from given URL
            Function Get-SPOWebPermission([Microsoft.SharePoint.Client.Web]$Web)
            {
                #Get all immediate subsites of the site
                $Ctx.Load($web.Webs) 
                $Ctx.executeQuery()
       
                #Call the function to Get Lists of the web
                Write-host -f Yellow "Getting the Permissions of Web "$Web.URL"..."
      
                #Check if the Web has unique permissions
                $Web.Retrieve("HasUniqueRoleAssignments")
                $Ctx.ExecuteQuery()
      
                #Get the Web's Permissions
                If($web.HasUniqueRoleAssignments -eq $true)
                {
                    Get-Permissions -Object $Web
                }
      
                #Scan Lists with Unique Permissions
                Write-host -f Yellow "`t Getting the Permissions of Lists and Libraries in "$Web.URL"..."
                Get-SPOListPermission($Web)
       
                #Iterate through each subsite in the current web
                Foreach ($Subweb in $web.Webs)
                {
                     #Call the function recursively                           
                     Get-SPOWebPermission($SubWeb)
                }
            }
      
            #Call the function with RootWeb to get site collection permissions
            Get-SPOWebPermission $Web
      
            Write-host -f Green "Site Permission Report Generated Successfully!"
         }
        Catch {
            write-host -f Red "Error Generating Site Permission Report!" $_.Exception.Message
       }
    }
      
    #Set parameter values
    $SiteURL="https://crescent.sharepoint.com"
    $ReportFile="C:\Temp\SitePermissionRpt.csv"
    $BatchSize = 500
      
    #Call the function
    Generate-SPOSitePermissionRpt -SiteURL $SiteURL -ReportFile $ReportFile
    
    

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful