3

I wrote a script in Powershell to reset the role inheritance of multiple files in a Sharepoint document library.

Worked fine until I ran across a library with more than 5000 files. Found a solution for this and kept going.

The script works fine but every now and then it throws a random error and I have to start over. This is fine as long as there isn't that many files, I can just run it again.

Now, I have a library with 1.3M files (I know...). The chance of crashing while going through this is almost 100% and restarting it will take way to long. So, I figured I would run thru one folder at a time instead which would bring down the number of files per run. Doing this introduces the threshold of 5000 files again though, any idea on how to bypass this when going through a folder at a time?

$url = 'https://domain.sharepoint.com/'
$ListTitle = 'List Name'
$folderName = 'Folder Name'

# Paths to SDK. Please verify location on your computer.
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"

# Login
$ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$username = "[email protected]"
$AdminPassword = "my password"
$password = ConvertTo-SecureString -string $AdminPassword -AsPlainText -Force
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

# Fetch list
$ll=$ctx.Web.Lists.GetByTitle($ListTitle)
$ctx.Load($ll) 
$ctx.ExecuteQuery()

# Find folder
$folder = $ctx.Web.GetFolderByServerRelativeUrl($ctx.Web.ServerRelativeUrl + $listTitle + '/' + $folderName + '/')
$ctx.Load($folder)
$ctx.ExecuteQuery();

# Fetch items
$spQuery = New-Object Microsoft.SharePoint.Client.CamlQuery

## View XML
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@
## Page Position
$position = $null

## All Items
$allItems = @()
Do
{
    $spQuery.ListItemCollectionPosition = $position
    $spQuery.ViewXml = $qCommand
    ## Executing the query
    $spQuery.FolderServerRelativeUrl = $folder.ServerRelativeUrl
    $itemki = $ll.GetItems($spQuery)
    $ctx.Load($itemki)
    $ctx.ExecuteQuery()

    # Loop through all items
    $noOfObject = $itemki.Count
    for($j=0;$j -lt $itemki.Count ;$j++)
    {        
        $itemki[$j].ResetRoleInheritance()
        if($j % 500 -eq 0) # Processing 500 items at a time.
        {
            $ctx.ExecuteQuery()
        }
    }
    $ctx.ExecuteQuery()

    ## Getting the position of the previous page
    $position = $itemki.ListItemCollectionPosition
    Write-Host $position.PagingInfo $position.TypeId

    # Adding current collection to the allItems collection
    $allItems += $itemki
}
# the position of the last page will be Null
Until($position -eq $null)
Improve this question

2 Answers 2

Reset to default
1

Hi why not take another approach and use SharePoint search + rest api inside our script? The SharePoint Search can deal with large information quantity and can do teh job for query. I´ve already have the same problem and solved with it

Improve this answer
5
  • Haven't heard of that before. Could you elaborate a little bit or point me in the right direction? Commented Mar 22, 2018 at 15:34
  • How would you achieve this? There is no option to query for security scopes via search... Retrieving all items does not seem to be a real benefit here Commented Mar 22, 2018 at 20:07
  • Sorry for answer so late, busy day. Ok use the serch to get the items, just swicth the original list query mechanism for sharepoint search. It will ask you to change you scrip. With serach you gona have itens (id). with item id you can get Itemby ID and process it. The erros you are getting right now, in my point of view, came form sharepoint CAML query limitations. Commented Mar 22, 2018 at 22:29
  • Heiko the point is not to query for secury scopes but have items ID in hand to process items. If you can have it sorted you can process it even in parallel.. have 5 or 10 instances doing the jobs in teh same time Commented Mar 22, 2018 at 22:32
  • I agree with Edvaldo here. Searching is the only reasonable solution and it has worked well for me in the past too. I have not used REST though, I used Microsoft.SharePoint.Client.Search.Query.KeywordQuery and passed in a a startrow and rowlimit. One thing you will also need though is a backoff for ctx.execute() as that number of queries will get you throttled. Commented Mar 23, 2018 at 10:39
0

You could persist the last processed ID once per 5000 Bach and add it to the query as an additional filter. The ID is indexed OOTB so that should work...

Also you can try to retrieve the items with a broken permissions by retrieving all the security scopes from the DB (grrr... You said online - sorry - remove that)

Improve this answer
2
  • Could you perhaps elaborate on how to add starting ID to the query? Commented Mar 23, 2018 at 18:56
  • You remember the max of the ID value in each batch. Then you modify the query to return only items with an ID greater than that in an additional where clause. If you font have an ID you could use gt 0 as the same condition. ID is indexed, so this should cause no problem. Edit: Dont forget to sort by ID also for this to work. (which you already do) Commented Mar 25, 2018 at 9:50

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.