Run reports on All users permissions in SharePoint Online with PowerShell
If you are managing the SharePoint online environment there pretty high chances that you would need to run the report on users who have access to the site / list / library and all sub-sites or sub-items. If you have a pretty complex structure of sites / lists and permissions you wouldn't like to spend all your time by manually going and checking permission. Also, if you don't have any reporting tools like ShareGate - PowerShell would be the best option here.
So, bellow is the PowerShell script to run 4 different permission reports on your SharePoint online site.
Report options: - Quick check (Root-Site) - Moderate (Object items and Inherited Permissions excluded) - Moderate (Object items excluded) - Full check (all sub-sites and items)
Pre-requisitions:- You need to have at least site collection admin permissions - Run PowerShell ISE with admin privileges on your local machine - Install pnp
Install-Module SharePointPnPPowerShellOnline
Here is the script to run
At the bottom you will find parameters on which report you would like to generate, you need to uncomment the according line.
Also, you need to provide your site URL and path to the folder very libs located and reports will be generated too. $SiteURL =""
$ReportPath = ""
$LibsPath = ""
You don't need to provide your credentials in the script as it uses web authentication.
The script will generate the excel file as the output in your specified ReportPath folder.
You can download the complete zip archive from here too.
cls
Function Load-Dependencies
{
#If you are working in the PowerShell and still have error 0x80131515, reopen console after unblocking files
Unblock-File -Path $LibsPath'\Microsoft.SharePoint.Client.dll'
Unblock-File -Path $LibsPath'\Microsoft.SharePoint.Client.Runtime.dll'
Unblock-File -Path $LibsPath'\Microsoft.Online.SharePoint.Client.Tenant.dll'
Unblock-File -Path $LibsPath'\Newtonsoft.Json.dll'
Unblock-File -Path $LibsPath'\Microsoft.Graph.dll'
Unblock-File -Path $LibsPath'\Microsoft.IdentityModel.Clients.ActiveDirectory.dll'
Unblock-File -Path $LibsPath'\OfficeDevPnP.Core.dll'
try
{
Add-Type -Path $LibsPath'\Microsoft.SharePoint.Client.dll'
Add-Type -Path $LibsPath'\Microsoft.SharePoint.Client.Runtime.dll'
Add-Type -Path $LibsPath'\Microsoft.Online.SharePoint.Client.Tenant.dll'
Add-Type -Path $LibsPath'\Newtonsoft.Json.dll'
Add-Type -Path $LibsPath'\Microsoft.Graph.dll'
Add-Type -Path $LibsPath'\Microsoft.IdentityModel.Clients.ActiveDirectory.dll'
Add-Type -Path $LibsPath'\OfficeDevPnP.Core.dll'
}
catch [System.Reflection.ReflectionTypeLoadException]
{
Write-Host -f Red "Message: $($_.Exception.Message)"
Write-Host "StackTrace: $($_.Exception.StackTrace)"
Write-Host -f DarkYellow "LoaderExceptions: $($_.Exception.LoaderExceptions)"
}
}
#Function to call a non-generic method Load
Function Invoke-LoadMethod([Microsoft.SharePoint.Client.ClientObject]$Object =
$(throw "Please provide a Client Object"),[string]$PropertyName)
{
$Ctx = $Object.Context
$Load = [Microsoft.SharePoint.Client.ClientContext].GetMethod("Load")
$Type = $Object.GetType()
$ClientLoad = $load.MakeGenericMethod($type)
$Parameter = [System.Linq.Expressions.Expression]::Parameter(($type), $type.Name)
$Expression = [System.Linq.Expressions.Expression]
::Lambda([System.Linq.Expressions.Expression]
::Convert([System.Linq.Expressions.Expression]
::PropertyOrField($Parameter,$PropertyName),[System.Object] ), $($Parameter))
$ExpressionArray = [System.Array]::CreateInstance($Expression.GetType(), 1)
$ExpressionArray.SetValue($Expression, 0)
$ClientLoad.Invoke($ctx,@($Object,$ExpressionArray))
}
#Function to Get Permissions Applied on a particular Object, such as: Web or List
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;
$ObjectTitle = $Object.Title }
"Microsoft.SharePoint.Client.ListItem"
{
If($Object.FileSystemObjectType -eq "Folder")
{
$ObjectType = "Folder"
#Get the URL of the Folder
Invoke-LoadMethod -Object $Object -PropertyName "Folder"
$Ctx.ExecuteQuery()
$ObjectTitle = $Object.Folder.Name
$ObjectURL = $("{0}{1}" -f $Ctx.Web.Url.Replace($Ctx.Web.ServerRelativeUrl,'')
,$Object.Folder.ServerRelativeUrl)
}
Else #File or List Item
{
#Get the URL of the Object
Invoke-LoadMethod -Object $Object -PropertyName "File"
$Ctx.ExecuteQuery()
If($Object.File.Name -ne $Null)
{
$ObjectType = "File"
$ObjectTitle = $Object.File.Name
$ObjectURL = $("{0}{1}" -f $Ctx.Web.Url.Replace($Ctx.Web.ServerRelativeUrl,'')
,$Object.File.ServerRelativeUrl)
}
else
{
$ObjectType = "List Item"
$ObjectTitle = $Object["Title"]
#Get the URL of the List Item
Invoke-LoadMethod -Object $Object.ParentList -PropertyName "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 or Library"
$ObjectTitle = $Object.Title
#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)
}
}
#Check if Object has unique permissions
Invoke-LoadMethod -Object $Object -PropertyName "HasUniqueRoleAssignments"
$Ctx.ExecuteQuery()
$HasUniquePermissions = $Object.HasUniqueRoleAssignments
#Get permissions assigned to the object
$RoleAssignments = $Object.RoleAssignments
$Ctx.Load($RoleAssignments)
$Ctx.ExecuteQuery()
#Loop through each permission assigned and extract details
$PermissionCollection = @()
Foreach($RoleAssignment in $RoleAssignments)
{
$Ctx.Load($RoleAssignment.Member)
$Ctx.executeQuery()
#Get the Principal Type: User, SP Group, AD Group
$PermissionType = $RoleAssignment.Member.PrincipalType
#Get the Permission Levels assigned
$Ctx.Load($RoleAssignment.RoleDefinitionBindings)
$Ctx.ExecuteQuery()
$PermissionLevels = $RoleAssignment.RoleDefinitionBindings | Select -ExpandProperty Name
#Remove Limited Access
$PermissionLevels = ($PermissionLevels | Where { $_ –ne "Limited Access"}) -join ","
If($PermissionLevels.Length -eq 0) {Continue}
#Get SharePoint group members
If($PermissionType -eq "SharePointGroup")
{
#Get Group Members
$Group = $Ctx.web.SiteGroups.GetByName($RoleAssignment.Member.LoginName)
$Ctx.Load($Group)
$GroupMembers= $Group.Users
$Ctx.Load($GroupMembers)
$Ctx.ExecuteQuery()
If($GroupMembers.count -eq 0){Continue}
$GroupUsersTMP = ($GroupMembers | Select Title, Email)
$b = New-Object System.Collections.ArrayList
$GroupUsersTMP | ForEach {$b.Add($_.Title + " `"" + $_.Email + "`"")}
$GroupUsers = $b
#Add the Data to Object
$Permissions = New-Object PSObject
$Permissions | Add-Member NoteProperty Object($ObjectType)
$Permissions | Add-Member NoteProperty Title($ObjectTitle)
$Permissions | Add-Member NoteProperty URL($ObjectURL)
$Permissions | Add-Member NoteProperty HasUniquePermissions($HasUniquePermissions)
$Permissions | Add-Member NoteProperty Users($GroupUsers)
$Permissions | Add-Member NoteProperty Type($PermissionType)
$Permissions | Add-Member NoteProperty Permissions($PermissionLevels)
$Permissions | Add-Member NoteProperty GrantedThrough("SharePoint Group: $($RoleAssignment.Member.LoginName)")
$PermissionCollection += $Permissions
}
Else
{
$c = $RoleAssignment.Member.LoginName
$d=$c.split("|")
$LoginName = $d[2]
#Add the Data to Object
$Permissions = New-Object PSObject
$Permissions | Add-Member NoteProperty Object($ObjectType)
$Permissions | Add-Member NoteProperty Title($ObjectTitle)
$Permissions | Add-Member NoteProperty URL($ObjectURL)
$Permissions | Add-Member NoteProperty HasUniquePermissions($HasUniquePermissions)
$Permissions | Add-Member NoteProperty Users($RoleAssignment.Member.Title + " `"" + $LoginName + "`"")
$Permissions | Add-Member NoteProperty Type($PermissionType)
$Permissions | Add-Member NoteProperty Permissions($PermissionLevels)
$Permissions | Add-Member NoteProperty GrantedThrough("Direct Permissions")
$PermissionCollection += $Permissions
}
}
#Export Permissions to CSV File
$PermissionCollection | Export-CSV $ReportFile -NoTypeInformation -Append
}
#Function to get sharepoint online site permissions report
Function Generate-SPOSitePermissionRpt()
{
[cmdletbinding()]
Param
(
[Parameter(Mandatory=$false)] [String] $SiteURL,
[Parameter(Mandatory=$false)] [String] $ReportFile,
[Parameter(Mandatory=$false)] [switch] $Recursive,
[Parameter(Mandatory=$false)] [switch] $ScanItemLevel,
[Parameter(Mandatory=$false)] [switch] $IncludeInheritedPermissions
)
Try {
#Get Credentials to connect
$authManager = New-Object OfficeDevPnP.Core.AuthenticationManager($null)
#Setup the context
$Ctx = $authManager.GetWebLoginClientContext($SiteURL)
#Get the Web & Root Web
$Web = $Ctx.Web
$RootWeb = $Ctx.Site.RootWeb
$Ctx.Load($Web)
$Ctx.Load($RootWeb)
$Ctx.ExecuteQuery()
Write-host -f Yellow "Getting Site Collection Administrators..."
#Get Site Collection Administrators
$SiteUsers= $RootWeb.SiteUsers
$Ctx.Load($SiteUsers)
$Ctx.ExecuteQuery()
$SiteAdmins = $SiteUsers | Where { $_.IsSiteAdmin -eq $true}
$SiteCollectionAdminsTMP = ($SiteAdmins | Select Title, Email)
$a = New-Object System.Collections.ArrayList
$SiteCollectionAdminsTMP | ForEach {$a.Add($_.Title + " `"" + $_.Email + "`"")}
$SiteCollectionAdmins = $a
#Add the Data to Object
$Permissions = New-Object PSObject
$Permissions | Add-Member NoteProperty Object("Site Collection")
$Permissions | Add-Member NoteProperty Title($RootWeb.Title)
$Permissions | Add-Member NoteProperty URL($RootWeb.URL)
$Permissions | Add-Member NoteProperty HasUniquePermissions("TRUE")
$Permissions | Add-Member NoteProperty Users($SiteCollectionAdmins)
$Permissions | Add-Member NoteProperty Type("Site Collection Administrators")
$Permissions | Add-Member NoteProperty Permissions("Site Owner")
$Permissions | Add-Member NoteProperty GrantedThrough("Direct Permissions")
#Export Permissions to CSV File
$Permissions | Export-CSV $ReportFile -NoTypeInformation
#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 = "$BatchSize"
$ItemCounter = 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)
{
#Get Objects with Unique Permissions or Inherited Permissions based
on 'IncludeInheritedPermissions' switch
If($IncludeInheritedPermissions)
{
Get-Permissions -Object $ListItem
}
Else
{
Invoke-LoadMethod -Object $ListItem -PropertyName "HasUniqueRoleAssignments"
$Ctx.ExecuteQuery()
If($ListItem.HasUniqueRoleAssignments -eq $True)
{
#Call the function to generate Permission report
Get-Permissions -Object $ListItem
}
}
$ItemCounter++
Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100)
-Activity "Processing Items $ItemCounter 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()
#Exclude system lists
$ExcludedLists = @("Access Requests","App Packages","appdata","appfiles","Apps in Testing",
"Cache Profiles","Composed Looks","Content and Structure Reports","Content type publishing error log","Converted Forms",
"Device Channels","Form Templates","fpdatasources","Get started with Apps for Office
and SharePoint","List Template Gallery", "Long Running Operation Status","Maintenance Log Library",
"Images", "site collection images"
,"Master Docs","Master Page Gallery","MicroFeed","NintexFormXml","Quick Deploy Items",
"Relationships List","Reusable Content","Reporting Metadata", "Reporting Templates",
"Search Config List","Site Assets","Preservation Hold Library"
"Site Pages", "Solution Gallery","Style Library","Suggested Content Browser Locations",
"Theme Gallery", "TaxonomyHiddenList","User Information List","Web Part Gallery","wfpub",
"wfsvc","Workflow History","Workflow Tasks", "Pages")
$Counter = 0
#Get all lists from the web
ForEach($List in $Lists)
{
#Exclude System Lists
If($List.Hidden -eq $False -and $ExcludedLists -notcontains $List.Title)
{
$Counter++
Write-Progress -PercentComplete ($Counter / ($Lists.Count) * 100)
-Activity "Processing Lists $Counter of $($Lists.Count) in $($Web.URL)"
-Status "Exporting Permissions from List '$($List.Title)'"
#Get Item Level Permissions if 'ScanItemLevel' switch present
If($ScanItemLevel)
{
#Get List Items Permissions
Get-SPOListItemsPermission -List $List
}
#Get Lists with Unique Permissions or Inherited Permissions based
on 'IncludeInheritedPermissions' switch
If($IncludeInheritedPermissions)
{
Get-Permissions -Object $List
}
Else
{
#Check if List has unique permissions
Invoke-LoadMethod -Object $List -PropertyName "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 permissions of the web
Write-host -f Yellow "Getting Permissions of the Web: $($Web.URL)..."
Get-Permissions -Object $Web
#Get List Permissions
Write-host -f Yellow "`t Getting Permissions of Lists and Libraries..."
Get-SPOListPermission($Web)
#Recursively get permissions from all sub-webs based on the "Recursive" Switch
If($Recursive)
{
#Iterate through each subsite in the current web
Foreach ($Subweb in $web.Webs)
{
#Get Webs with Unique Permissions or Inherited Permissions based
on 'IncludeInheritedPermissions' switch
If($IncludeInheritedPermissions)
{
Get-SPOWebPermission($Subweb)
}
Else
{
#Check if the Web has unique permissions
Invoke-LoadMethod -Object $Subweb -PropertyName "HasUniqueRoleAssignments"
$Ctx.ExecuteQuery()
#Get the Web's Permissions
If($Subweb.HasUniqueRoleAssignments -eq $true)
{
#Call the function recursively
Get-SPOWebPermission($Subweb)
}
}
}
}
}
#Call the function with RootWeb to get site collection permissions
Get-SPOWebPermission $Web
Write-host -f Green "`n*** Site Permission Report Generated Successfully!***"
}
Catch {
write-host -f Red "Error Generating Site Permission Report!" $_.Exception.Message
}
}
#region ***Parameters***
$SiteURL="https://CONTOSO.sharepoint.com/sites/dev"
$BatchSize = 500
$ReportPath = "D:\SharePoint-Online-Permission-Checker\Reports"
$LibsPath = "D:\SharePoint-Online-Permission-Checker\Libs"
$LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
$ReportFile = $ReportPath + '\SitePermissionRpt_' + $LogTime + '.csv'
#Credential settings
#$CurrentUserName = whoami /upn
#$userName = "UserName@CONTOSO.COM"
#$securePassword = ConvertTo-SecureString -String "PASSWORD" -AsPlainText -Force
#endregion
Load-Dependencies
#Call the function to generate permission report
#Quick check (Root-Site)
#Generate-SPOSitePermissionRpt -SiteURL $SiteURL -ReportFile $ReportFile
#Moderate (Object items and Inherited Permissions excluded)
#Generate-SPOSitePermissionRpt -SiteURL $SiteURL -ReportFile $ReportFile -Recursive
#Moderate (Object items excluded)
#Generate-SPOSitePermissionRpt -SiteURL $SiteURL -ReportFile $ReportFile -Recursive -IncludeInheritedPermissions
#Full check
#Generate-SPOSitePermissionRpt -SiteURL $SiteURL -ReportFile $ReportFile -Recursive -ScanItemLevel -IncludeInheritedPermissions
