Forum Discussion

Deenadayalan V's avatar
Deenadayalan V
Brass Contributor
Oct 17, 2017
Solved

Not able to Stop Inherit permissions due to View Threshold limit

Hi All,

 

We moved our SP 2007 huge site to SP Online, but doing so the parent permissions are inherited to the subsites and the document libraries as well while they have their unique permissions.

 

Now we want to break inheritance, but since the number of items are more than 99 K, we are getting excpetion message that threshold limit is breached and hence cannot execute the operation.

 

What are the best ways to handle the scenario? (without breaking them into multiple lists/folders)

 

Appreciate the suggestions

  • Sam Larson's avatar
    Sam Larson
    Dec 10, 2018
    CSOM Script for Breaking Inheritance on Large List
    ## DISCLAIMER:
    ## Copyright (c) Microsoft Corporation. All rights reserved. This
    ## script is made available to you without any express, implied or
    ## statutory warranty, not even the implied warranty of
    ## merchantability or fitness for a particular purpose, or the
    ## warranty of title or non-infringement. The entire risk of the
    ## use or the results from the use of this script remains with you.
    ##ps1
    ## Filename:    CSOM-BreakInheritanceLargeList.ps1
    ## Author:      salarson
    ## Description: Breaks Inheritcane on all items in  Large List 
    ## Required: Download and install SPO PowerShell Module - http://www.microsoft.com/en-us/download/details.aspx?id=35588
    #####################################################################
    
    $loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
    $loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
    
    $username = Read-Host -Prompt "Username"
    $password = Read-Host -Prompt "Password for $username" -AsSecureString
    
    $siteurl = Read-Host -Prompt "Enter Site Collection URL"
    $listTitle = Read-Host -Prompt "Enter List Title"
    
    
    $ctx = New-Object Microsoft.SharePoint.client.ClientContext($siteurl)
    
    $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
    
    
    
    $ll=$ctx.Web.Lists.GetByTitle($ListTitle)
    
      $ctx.Load($ll)
    
      $ctx.ExecuteQuery()
    function ResetRoleInheritanceLargeList($list)
    {
        $global:listItemCountFromProperty = $null
        $global:listitems = $null
    
        $global:listItemCountFromProperty = $list.itemcount
        $global:isComplete = $false
        [int]$global:loopcount = 0
        [int]$global:itemsfoundcount = 0
        [int]$batchsize = 250
        [int]$global:maxloops = 1000
        [int]$global:lowerbound = 0
        [int]$global:upperbound = 0
    
    
        Write-Host "==RESETTING ROLE INHERITANCE ON ALL ITEMS IN ALL FOLDERS==" -ForegroundColor Green
    
        if ($global:listItemCountFromProperty -gt 0)
        {
           
               try
               {
                    write-host "List item count = $($list.itemcount)"
                             while ($global:isComplete -ne $true)
                             {
                                   if (($global:loopcount -le $global:maxloops) -and ($global:itemsfoundcount -lt $global:listItemCountFromProperty))
                                   {
                                          $global:lowerbound = ($global:loopcount * $batchsize)
                                          $global:upperbound = (($global:loopcount + 1) * $batchsize)
                                          $global:loopcount = $global:loopcount + 1
                                          #Write-Host ""
                                          #Write-Host "Looking for items with IDs between $($global:lowerbound) and $($global:upperbound)"
                                          $query = new-object Microsoft.SharePoint.Client.Camlquery
                                          $query.ViewXml = "<View Scope=`"RecursiveAll`">
                                          <Query>
                                          <Where>
                                                 <And>
                                                        <Geq>
                                                               <FieldRef Name='ID' />
                                                               <Value Type='Counter'>$($global:lowerbound)</Value>
                                                        </Geq>
                                                        <Lt>
                                                               <FieldRef Name='ID' />
                                                               <Value Type='Counter'>$($global:upperbound)</Value>
                                                        </Lt>
                                                 </And>
                                          </Where>
                                          </Query>
                                   </View>"
    
    
                                      
                                          $items = $null
                                          $items = $list.GetItems($query)
                                          $ctx.load($items)
                                          $ctx.executeQuery()
                                      
                                          $global:itemsfoundcount = $global:itemsfoundcount + $items.count
                                          
                                       #Write-Host "Current total items found count = $($global:itemsfoundcount) out of $($global:listItemCountFromProperty)."
                                          #Write-Host "Current batch items found count = $($items.count)."
                                          #Write-Host "=="
                                          foreach ($item in $items)
                                          {
                                                  write-host "Updating item with ID: $($item.id)."
                                                  $item.resetroleinheritance()
                                 $item.update()
                                 $ctx.ExecuteQuery()                           
                                             
                                          }
                                      
                                   }
                               
                                   else
                                   {
                                          $global:isComplete = $true
                                          <#
                            Write-Host ""
                                          Write-Host "Export Complete."
                                          Write-Host ""
                                          Write-Host "Sorting objects"
                                      
                                          $sorteddocumentobjects = $global:documentitempropertyobjects | Sort-Object "FileRef"
                                          $sortedfolderobjects = $global:folderitempropertyobjects | Sort-Object "FileRef"
                                          $sortedallobjects = $global:listitempropertyobjects| Sort-Object "FileRef"
                                      
                                          #$global:folderitempropertyobjects
                                      
                                          Write-Host ""
                                           Write-Host "Writing output to csv files."
                                          Write-Host ""
                               
                                       $sortedallobjects| ConvertTo-Csv -NoTypeInformation -delimiter "`t" > "sortedTotalList.csv"
                                       $sortedfolderobjects| ConvertTo-Csv -NoTypeInformation -delimiter "`t" > "sortedFolderUrls.csv"
                                       $sorteddocumentobjects| ConvertTo-Csv -NoTypeInformation -delimiter "`t" > "sortedDocumentUrls.csv"
                            #>
                                          #Write-Host "Documents updated."
                                          Write-Host "Total number of list items found and processed (including folders) = $($global:itemsfoundcount)."
                                          #Write-Host "Checked items in batches of $($batchsize) up to list item with ID less than: $($global:upperbound)"
                                   }
                             }
                      }
           
               catch
               {
                      Write-host ""
                      Write-host "Exception thrown!"
                      write-host "Error is: $($_)"
                      Write-Host "Current item number processed (if any) = $($global:itemsfoundcount)"
                      write-host ""
               }
        }
        else
        {
               Write-Host ""
               Write-Host "No items found."
        }
    
    }
    
    ResetRoleInheritanceLargeList $ll 
    
    Write-host “END OF SCRIPT”

4 Replies

  • Overall I'd suggest breaking them down before you migrate in the future, other than that we can do some CAML queries with CSOM to get around this, I'll PM you a script that should do the trick.
    • webuildwood's avatar
      webuildwood
      Copper Contributor

      Sam Larson can you send me the same CAML/CSOM script? My Engineering department just recently pushed about 170k items into a Document Library without breaking inheritance on that library first, and they are seeing this issue now. I'm looking for a way to handle this programmatically if I run into this issue in the future. Thanks in advance! 

      • Sam Larson's avatar
        Sam Larson
        Icon for Microsoft rankMicrosoft
        CSOM Script for Breaking Inheritance on Large List
        ## DISCLAIMER:
        ## Copyright (c) Microsoft Corporation. All rights reserved. This
        ## script is made available to you without any express, implied or
        ## statutory warranty, not even the implied warranty of
        ## merchantability or fitness for a particular purpose, or the
        ## warranty of title or non-infringement. The entire risk of the
        ## use or the results from the use of this script remains with you.
        ##ps1
        ## Filename:    CSOM-BreakInheritanceLargeList.ps1
        ## Author:      salarson
        ## Description: Breaks Inheritcane on all items in  Large List 
        ## Required: Download and install SPO PowerShell Module - http://www.microsoft.com/en-us/download/details.aspx?id=35588
        #####################################################################
        
        $loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
        $loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
        
        $username = Read-Host -Prompt "Username"
        $password = Read-Host -Prompt "Password for $username" -AsSecureString
        
        $siteurl = Read-Host -Prompt "Enter Site Collection URL"
        $listTitle = Read-Host -Prompt "Enter List Title"
        
        
        $ctx = New-Object Microsoft.SharePoint.client.ClientContext($siteurl)
        
        $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
        
        
        
        $ll=$ctx.Web.Lists.GetByTitle($ListTitle)
        
          $ctx.Load($ll)
        
          $ctx.ExecuteQuery()
        function ResetRoleInheritanceLargeList($list)
        {
            $global:listItemCountFromProperty = $null
            $global:listitems = $null
        
            $global:listItemCountFromProperty = $list.itemcount
            $global:isComplete = $false
            [int]$global:loopcount = 0
            [int]$global:itemsfoundcount = 0
            [int]$batchsize = 250
            [int]$global:maxloops = 1000
            [int]$global:lowerbound = 0
            [int]$global:upperbound = 0
        
        
            Write-Host "==RESETTING ROLE INHERITANCE ON ALL ITEMS IN ALL FOLDERS==" -ForegroundColor Green
        
            if ($global:listItemCountFromProperty -gt 0)
            {
               
                   try
                   {
                        write-host "List item count = $($list.itemcount)"
                                 while ($global:isComplete -ne $true)
                                 {
                                       if (($global:loopcount -le $global:maxloops) -and ($global:itemsfoundcount -lt $global:listItemCountFromProperty))
                                       {
                                              $global:lowerbound = ($global:loopcount * $batchsize)
                                              $global:upperbound = (($global:loopcount + 1) * $batchsize)
                                              $global:loopcount = $global:loopcount + 1
                                              #Write-Host ""
                                              #Write-Host "Looking for items with IDs between $($global:lowerbound) and $($global:upperbound)"
                                              $query = new-object Microsoft.SharePoint.Client.Camlquery
                                              $query.ViewXml = "<View Scope=`"RecursiveAll`">
                                              <Query>
                                              <Where>
                                                     <And>
                                                            <Geq>
                                                                   <FieldRef Name='ID' />
                                                                   <Value Type='Counter'>$($global:lowerbound)</Value>
                                                            </Geq>
                                                            <Lt>
                                                                   <FieldRef Name='ID' />
                                                                   <Value Type='Counter'>$($global:upperbound)</Value>
                                                            </Lt>
                                                     </And>
                                              </Where>
                                              </Query>
                                       </View>"
        
        
                                          
                                              $items = $null
                                              $items = $list.GetItems($query)
                                              $ctx.load($items)
                                              $ctx.executeQuery()
                                          
                                              $global:itemsfoundcount = $global:itemsfoundcount + $items.count
                                              
                                           #Write-Host "Current total items found count = $($global:itemsfoundcount) out of $($global:listItemCountFromProperty)."
                                              #Write-Host "Current batch items found count = $($items.count)."
                                              #Write-Host "=="
                                              foreach ($item in $items)
                                              {
                                                      write-host "Updating item with ID: $($item.id)."
                                                      $item.resetroleinheritance()
                                     $item.update()
                                     $ctx.ExecuteQuery()                           
                                                 
                                              }
                                          
                                       }
                                   
                                       else
                                       {
                                              $global:isComplete = $true
                                              <#
                                Write-Host ""
                                              Write-Host "Export Complete."
                                              Write-Host ""
                                              Write-Host "Sorting objects"
                                          
                                              $sorteddocumentobjects = $global:documentitempropertyobjects | Sort-Object "FileRef"
                                              $sortedfolderobjects = $global:folderitempropertyobjects | Sort-Object "FileRef"
                                              $sortedallobjects = $global:listitempropertyobjects| Sort-Object "FileRef"
                                          
                                              #$global:folderitempropertyobjects
                                          
                                              Write-Host ""
                                               Write-Host "Writing output to csv files."
                                              Write-Host ""
                                   
                                           $sortedallobjects| ConvertTo-Csv -NoTypeInformation -delimiter "`t" > "sortedTotalList.csv"
                                           $sortedfolderobjects| ConvertTo-Csv -NoTypeInformation -delimiter "`t" > "sortedFolderUrls.csv"
                                           $sorteddocumentobjects| ConvertTo-Csv -NoTypeInformation -delimiter "`t" > "sortedDocumentUrls.csv"
                                #>
                                              #Write-Host "Documents updated."
                                              Write-Host "Total number of list items found and processed (including folders) = $($global:itemsfoundcount)."
                                              #Write-Host "Checked items in batches of $($batchsize) up to list item with ID less than: $($global:upperbound)"
                                       }
                                 }
                          }
               
                   catch
                   {
                          Write-host ""
                          Write-host "Exception thrown!"
                          write-host "Error is: $($_)"
                          Write-Host "Current item number processed (if any) = $($global:itemsfoundcount)"
                          write-host ""
                   }
            }
            else
            {
                   Write-Host ""
                   Write-Host "No items found."
            }
        
        }
        
        ResetRoleInheritanceLargeList $ll 
        
        Write-host “END OF SCRIPT”

Resources