Skip to main content

SharePoint Farm Freezing, Becomes Unusable Without An IISReset - A Lesson In Item-Level Permissions

Recently I was working with a client who's SharePoint 2010 environment had been poorly managed for years.  They had a single site collection on a single, large content database (650GB).  Not uncommon, however this environment also occasionally froze up, never loading any of the pages on any sites until an IISRESET was performed on the Web Front End (WFE).

It started off only happening once a month, then moved to once a week, a few times a week, to 5-10 times a day...

Begin Troubleshooting:

The issue was around too many lists, and items within those lists having their own permissions.

Each permission for an item counts as one row in the RoleAssignment table in the database.

apparently they say a healthy DB will only have 200,000 rows in that table.

Ours had 10,000,000 rows (You can check this by right-clicking the Database in SQL Management Studio and clicking Reports > Disk Usage by Top Tables.  Then look for the RoleAssignment Table).  and every time someone went to check permissions on a big list, bang.  it locks the table while it finds the data (which takes ages to traverse), meanwhile every other computer trying to do anything on the site dies while the table is locked.

The ONLY way to fix this is to remove any tables with HEAPS of item level permissions.

I got two handy scripts for this.  both below:


Script #1: a SQL script to find the lists that had the most item-level permissions

##SQL QUERY TO FIND INDIVIDUAL SECURITY SCOPES - PROVIDED BY MICROSOFT
select COUNT(ra.PrincipalId) as [Count],p.ScopeUrl from RoleAssignment ra with(nolock) 
join Perms p with(nolock) 
on p.SiteId = ra.SiteId and p.ScopeId = ra.ScopeId 
group by p.ScopeUrl 
order by p.ScopeUrl desc


Script #2: a SharePoint Powershell script that checks if a list has item-level permissions, then gives you the option to set every item in the list to inherit the Lists permissions:
  • Restore-Inheritance.ps1 "http://SPURL/SITE" "LIST NAME" true
############################################################################### 

##  ADD IN SHAREPOINT SNAP IN IF NOT ALREADY LOADED ## 
############################################################################### 
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) { 
    Add-PSSnapin "Microsoft.SharePoint.PowerShell" 
}


############################################################################### 
##  SET VARIABLES FROM ARGUMENTS ## 
############################################################################### 

$webUrl = $args[0]
$listName = $args[1]
$listInherits = $args[2]

# Varibale to hold document count
$count = 0

############################################################################### 
##  OPEN OBJECTS & RESTORE INHERITANCE ## 
###############################################################################

try {
# Open the web and list objects
$web = Get-SPWeb $webUrl
$list = $web.Lists[$listName]

# If the list should inherit, reset the role inheritance
if ($listInherits -eq $true) {
$list.ResetRoleInheritance()
Write-Host "Updated permissions on list." -foregroundcolor Green
}

# Get all items with unique permissions
$itemsWithUniquePermissions = $list.GetItemsWithUniquePermissions()
Write-Host $itemsWithUniquePermissions.Count "number of items with unique permissions found."

# Only update items if some exist
if ($itemsWithUniquePermissions.Count -gt 0) {
foreach ($itemInfo in $itemsWithUniquePermissions) {
$item = $list.GetItemById($itemInfo.Id)
    $item.ResetRoleInheritance()
$count++
}

# Display number of items updated
Write-Host "Updated permissions on $count items." -foregroundcolor Green
}
else {
Write-Host "No items with unique permissions exist, nothing to update."
}

# Dispose of web object
$web.Dispose()
}
catch [Exception] {
Write-Host "Exception encountered.  Please ensure all arguments are valid." -foregroundcolor Red
Write-Host $_.Exception.Message -foregroundcolor Red
}


REFERENCES & THANKS
https://gallery.technet.microsoft.com/office/PowerShell-to-Reset-Unique-d885a93f 

Comments

Popular posts from this blog

Export Group Membership From Active Directory Using Power Query

If you need to export a list of staff from an Active Directory Group, follow these steps.  Allows you to gather all the names & usernames of staff in a particular group and save the information into Excel.

Step By Step How To:
Open up Excel > Power Query TabClick From Other Sources > From Active Directory:(In Excel 2016 this is found under: Data Tab > New Query > From Other Sources > From Active Directory) screenshots from Excel 2016 on left, Excel 2010 w/ Power Query Add-on right A popup will ask you to enter your domain name.  It should already be populated with the correct information but if not, input your companies domain name & Click OK:
In the Navigator that opens up, expand your domain tree and double click group:

You should now see a list of all groups in Active Directory in the Power Query Editor:
In order to find & filter for a specific Group, Click the Down-Arrow for the 'distinguishedName' column > Text Filters > Contains...
Type the name…

HP Laserjet 1022 Printer - Can't Print on Windows 10

If you are having trouble getting your laserjet working on Windows 10, here's how I resolved the problem:

Go to Devices & PrintersFind your printer & go to Printer Properties > Advanced TabSet Driver to: HP LaserJet 1022 Class Driver & ApplyIf HP LaserJet 1022 Class Driver is not available.  download from this site: http://support.hp.com/us-en/drivers/selfservice/HP-LaserJet-1000-Printer-series/439424/model/439431Print a test page.


SharePoint Online - Branding with CSS

-----------------------------------------------------------------------------------------
This post is related to a larger group of posts called Migrate SharePoint to Office 365 - Planning & Steps
-----------------------------------------------------------------------------------------

I've gone with a Publishing Site Collection so that I can use the 'Alternate CSS URL' in Site Settings > Master Page.

First off, thanks to the following websites which helped get me started:
http://blog.sharepointexperience.com/2015/02/sptechcon-austin-february-2015/#more-2766http://blog.sharepointexperience.com/2015/01/to-brand-or-not-to-brand/ Here's what the end result will look like (if you have the same fonts):


Now for the CSS...

/* Design By : Brett Randall*/
/*     Design Colours: Greens/Greys/Blues */
/*     Last Modified : 28/02/2017*/
/*     Description: CSS to rebrand SharePoint Online Publishing Site Collection that is using seattle.master */

/* TABLE OF CONTENTS
-- -…