Optimizing PowerShell Scripts to check for unique permissions in SharePoint: REST API vs. Get-PnPListItem

August 4, 2024

When working with large SharePoint sites, checking for unique permissions can be a time-consuming task. This blog post explores methods to optimize PowerShell scripts for fetching property HasUniqueRoleAssignments to determine unique permissions, including using PnP PowerShell and the SharePoint REST API. We compare their performance and highlight the advantages and limitations of each approach.

Using PnP PowerShell

PnP PowerShell provides an efficient way to interact with SharePoint Online and retrieve list items to check for unique permissions. Here’s a script that demonstrates how to use PnP PowerShell to achieve this:


	#Parameters

	$tenantUrl = Read-Host -Prompt "Enter tenant collection URL";
	$dateTime = (Get-Date).toString("dd-MM-yyyy-hh-ss")
	$invocation = (Get-Variable MyInvocation).Value
	$directorypath = Split-Path $invocation.MyCommand.Path
	$fileName = "SharedLinks-PnP" + $dateTime + ".csv"
	$ReportOutput = $directorypath + "\Logs\"+ $fileName

	#Connect to PnP Online
	Connect-PnPOnline -Url $tenantUrl -Interactive
	write-host $("Start time " + (Get-Date))
	$global:Results = @();

	function Get-ListItems_WithUniquePermissions{
	param(
	[Parameter(Mandatory)]
	[Microsoft.SharePoint.Client.List]$List
	)
	$selectFields = "ID,HasUniqueRoleAssignments,FileRef,FileLeafRef,FileSystemObjectType"

	$Url = $siteUrl + '/_api/web/lists/getbytitle(''' + $($list.Title) + ''')/items?$select=' + $($selectFields)
	$nextLink = $Url
	$listItems = @()
	$Stoploop =$true
	while($nextLink){
	do{
	try {
	$response = invoke-pnpsprestmethod -Url $nextLink -Method Get
	$Stoploop =$true

	}
	catch {
	write-host "An error occured: $_ : Retrying" -ForegroundColor Red
	$Stoploop =$true
	Start-Sleep -Seconds 30
	}
	}
	While ($Stoploop -eq $false)

	$listItems += $response.value \| where-object{$_.HasUniqueRoleAssignments -eq $true}
	if($response.'odata.nextlink'){
	$nextLink = $response.'odata.nextlink'
	} else{
	$nextLink = $null
	}
	}

	return $listItems
	}

	function getSharingLink($_object,$_type,$_siteUrl,$_listUrl)
	{
	$relativeUrl = $_object.FileRef
	$SharingLinks = if ($_type -eq 0) {
	Get-PnPFileSharingLink -Identity $relativeUrl
	} elseif ($_type -eq 1) {
	Get-PnPFolderSharingLink -Folder $relativeUrl
	}

	ForEach($ShareLink in $SharingLinks)
	{
	$result = New-Object PSObject -property $([ordered]@{
	SiteUrl = $_SiteURL
	listUrl = $_listUrl
	Name = $_object.FileLeafRef
	RelativeURL = $_object.FileRef
	ObjectType = $_Type -eq 1 ? "Folder" : "File"
	ShareId = $ShareLink.Id
	RoleList = $ShareLink.Roles -join "\|"
	Users = $ShareLink.GrantedToIdentitiesV2.User.Email -join "\|"
	ShareLinkUrl = $ShareLink.Link.WebUrl
	ShareLinkType = $ShareLink.Link.Type
	ShareLinkScope = $ShareLink.Link.Scope
	Expiration = $ShareLink.ExpirationDateTime
	BlocksDownload = $ShareLink.Link.PreventsDowload
	RequiresPassword = $ShareLink.HasPassword

	})
	$global:Results +=$result;
	}
	}

	#Exclude certain libraries
	$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")

	$m365Sites = Get-PnPTenantSite\| Where-Object { ( $_.Url -like '/sites/') -and $_.Template -ne 'RedirectSite#0' }
	$m365Sites \| ForEach-Object {
	$siteUrl = $_.Url;
	Connect-PnPOnline -Url $siteUrl -Interactive

	Write-Host "Processing site $siteUrl" -Foregroundcolor "Red";

	#getSharingLink $ctx $web "site" $siteUrl "";
	$ll = Get-PnPList -Includes BaseType, Hidden, Title,HasUniqueRoleAssignments,RootFolder \| Where-Object {$_.Hidden -eq $False -and $_.Title -notin $ExcludedLists } #$_.BaseType -eq "DocumentLibrary"
	Write-Host "Number of lists $($ll.Count)";

	foreach($list in $ll)
	{
	$listUrl = $list.RootFolder.ServerRelativeUrl;

	#Get all list items in batches
	$ListItems = Get-ListItems_WithUniquePermissions -List $list

	ForEach($item in $ListItems)
	{
	$type= $item.FileSystemObjectType;
	getSharingLink $item $type $siteUrl $listUrl;
	}
	}
	}

	$global:Results \| Export-CSV $ReportOutput -NoTypeInformation
	#Export-CSV $ReportOutput -NoTypeInformation
	Write-host -f Green "Sharing Links Report Generated Successfully!"
	write-host $("End time " + (Get-Date))
	#Order columns for CSV exportation
	# $sharedlinksResults \| select ServerRelativeUrl,Url,ShareTokenString,AllowsAnonymousAccess,ApplicationId,BlocksDownload,Created,CreatedBy,Description,Embeddable,Expiration,HasExternalGuestInvitees,Invitations,IsActive,IsAddressBarLink,IsCreateOnlyLink,IsDefault,IsEditLink,IsFormsLink,IsManageListLink,IsReviewLink,IsUnhealthy,LastModified,LastModifiedBy,LimitUseToApplication,LinkKind,PasswordLastModified,PasswordLastModifiedBy,RedeemedUsers,RequiresPassword,RestrictedShareMembership,Scope,ShareId,SharingLinkStatus,TrackLinkUsers \| ConvertTo-Csv -NoTypeInformation \| Out-File $ExportPath
	#Write-Host "Exported links giving access to files to path: '$($ExportPath)' with success!" -f Green

view raw GetHasUniqueRoleAssignments_PnP hosted with ❤ by GitHub

For a site with 1600 items, this script takes approximately 200 seconds. For larger sites with over 100,000 items, it takes over 2 hours, which is not optimal.

output of PnP

Using parameter Fields `HasUniqueRoleAssignments` with Get-PnPListItem

Specifying the Fields parameter to return values field HasUniqueRoleAssignments does not load the values of properties HasUniqueRoleAssignments without specifying the Id parameter.

$ListItems = Get-PnPListItem -List $list -PageSize 2000 -Fields "HasUniqueRoleAssignments"

Attempts to Optimize with CAML Query

Using CAML query to specify fields did not significantly improve performance and encountered list view threshold errors for lists with more than 5000 items.

$ListItems = Get-PnPListItem -List $list -Query "<View><ViewFields><FieldRef Name='HasUniqueRoleAssignments'/><FieldRef Name='FileRef'/><FieldRef Name='FileSystemObjectType'/><FieldRef Name='FileLeafRef'/></ViewFields><Query></Query></View>"

Using REST API

An alternative approach involves using the SharePoint REST API to query the HasUniqueRoleAssignments property.

REST API Endpoints

The following endpoints can be used to retrieve the HasUniqueRoleAssignments property:

/_api/web/HasUniqueRoleAssignments  
/_api/web/lists/getbytitle('list title')/HasUniqueRoleAssignments  
/_api/web/lists/getbytitle('list title')/items(id)/HasUniqueRoleAssignments

The query from the browser returns the HasUniqueRoleAssignments value.

https://reshmeeauckloo.sharepoint.com/sites/Company311/_api/web/lists/getbytitle('Documents')/items(5)?$Select=ID,HasUniqueRoleAssignments

HasUniqueRoleAssignments value returned from REST

Example REST API Script

Here’s a PowerShell script leveraging the REST API to retrieve unique permissions:


	param(
	[Parameter(Mandatory)]
	[string]$SiteUrl
	)

	if(!$siteUrl)
	{
	$siteUrl = Read-Host -Prompt "Enter the site collection";
	}

	#Parameters
	$dateTime = (Get-Date).toString("dd-MM-yyyy-hh-ss")
	$invocation = (Get-Variable MyInvocation).Value
	$directorypath = Split-Path $invocation.MyCommand.Path
	$fileName = "UniquePermissions_Rest-" + $dateTime + ".csv"
	$ReportOutput = $directorypath + "\Logs\"+ $fileName

	# Ensure the logs folder exists
	$logsFolder = Split-Path -Path $ReportOutput -Parent
	if (-not (Test-Path -Path $logsFolder)) {
	New-Item -Path $logsFolder -ItemType Directory
	}

	# Ensure the file exists
	if (-not (Test-Path -Path $ReportOutput)) {
	New-Item -Path $ReportOutput -ItemType File
	}

	#Exclude certain libraries
	$ExcludedLists = @("Access Requests", "App Packages", "appdata", "appfiles","Apps for SharePoint" ,"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")

	#$m365Sites = Get-PnPTenantSite\| Where-Object { ( $_.Url -like '/sites/') -and $_.Template -ne 'RedirectSite#0' }
	#$m365Sites \| ForEach-Object {
	#$siteUrl = $_.Url;
	Connect-PnPOnline -Url $siteUrl -Interactive
	write-host $("Start time " + (Get-Date))
	Write-Host "Processing site $siteUrl" -Foregroundcolor "Red";

	function Get-ListItems{
	param(
	[Parameter(Mandatory)]
	[Microsoft.SharePoint.Client.List]$List
	)

	$token = Get-PnPappauthaccesstoken
	$selectFields = "ID,HasUniqueRoleAssignments,FileRef,FileLeafRef,FileSystemObjectType"
	$headers = @{"Accept" = "application/json;odata=verbose"
	"Authorization" = "Bearer $token"}

	$Url = $siteUrl + '/_api/web/lists/getbytitle(''' + $($list.Title) + ''')/items?$select=' + $($selectFields)
	$nextLink = $Url
	$listItems = @()
	$Stoploop =$true
	while($nextLink){
	do{
	try {
	$response = invoke-pnpsprestmethod -Url $nextLink -Method Get
	$Stoploop =$true

	}
	catch {
	write-host "An error occured: $_ : Retrying" -ForegroundColor Red
	$Stoploop =$true
	Start-Sleep -Seconds 30
	}
	}
	While ($Stoploop -eq $false)

	$listItems += $response.value \| where-object{$_.HasUniqueRoleAssignments -eq $true}
	if($response.'odata.nextlink'){
	$nextLink = $response.'odata.nextlink'
	} else{
	$nextLink = $null
	}
	}

	return $listItems
	}

	$ll = Get-PnPList -Includes BaseType, Hidden, Title,HasUniqueRoleAssignments,RootFolder \| Where-Object {$_.Hidden -eq $False -and $_.Title -notin $ExcludedLists } #$_.BaseType -eq "DocumentLibrary"
	Write-Host "Number of lists $($ll.Count)";

	foreach($list in $ll)
	{
	$listUrl = $list.RootFolder.ServerRelativeUrl;
	$ListItems = Get-ListItems -List $list
	ForEach($item in $ListItems)
	{
	# add items to the report to export
	$item \| Select-Object @{Name="SiteURL";Expression={$siteUrl}},@{Name="ListTitle";Expression={$list.Title}},@{Name="ListUrl";Expression={$listUrl}},@{Name="ItemID";Expression={$item.ID}},@{Name="ItemURL";Expression={$siteUrl + $item.FileRef}},@{Name="ItemName";Expression={$item.FileLeafRef}} \| Export-Csv -Path $ReportOutput -Append -NoTypeInformation
	}
	}
	write-host $("End time " + (Get-Date))

view raw GetHasUniqueRoleAssignments_REST hosted with ❤ by GitHub

For a site with 1600 items, this script takes only 8 seconds.

Output REST

For larger sites with over 100,000 items, it completes in under 11 minutes, significantly faster than the PnP PowerShell approach with retry mechanism under the hood to handle throttling

REST Unique Permissions with retry

Microsoft Graph does not return the property HasUniqueRoleAssignments

Microsoft Graph does not return the HasUniqueRoleAssignments property, limiting its utility for this specific task.

Graph does not have property HasUniqueRoleAssignments

Performance comparison

Endpoint Type	Number of items	Timing
REST API	1600	8 secs
PnP PowerShell without any batch	1600	200 secs
REST API	100,000	11 mins
PnP PowerShell without any batch	100,000	2 hours
Microsoft Graph	`N/A`	`N/A`

Conclusion

Using the SharePoint REST API to check for unique permissions is significantly faster and more efficient than using PnP PowerShell, especially for large sites. This approach can save considerable time.

References

Invoke-PnPSPRestMethod

SharePoint REST API to retrieve ACLs of a SharePoint object

Get list of SharePoint files with unique permissions