Clarifying the Security Scope limits in SharePoint lists

Joel Plaut

March 19, 2012

All Post

Historically, Microsoft has defined it along the more conservative definition as “A scope is the security boundary for a securable object and any of its children that do not have a separate security boundary defined.”.  This implies that 10,000 uniquely assigned documents amongst five people (5*4*3*2*1 = 5! = 120) would be 10,000 security permissions, and not some number <=120.

https://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx

After the number of unique security scopes exceeds the value of the List Query Size Threshold, SharePoint uses a code path that requires additional SQL round trips to analyze the scopes before rendering a view.  So the impact is on viewing the list of documents (and providing the security trimmed view), hence my suggestion in such situations to consider retaining or even reducing the SharePoint default View limit of 30 items per page.

Use of Dynamic SQL for SSIS Data Sources

To test this out, I did an extract from a Content DB that houses a Document Library I have handy with many custom permissions divided amongst eight users. Probing the underlying data, it has a 18 distinct ACLs among 7K+ “Scopes” each with a unique ScopeURL and ScopeID (the internal Microsoft field names). Even though the ACLs are identical (SharePoint uses an ACL ID that I can pivot on to group the Scopes) each folder and often document has a unique scope, because of the way I had broken inheritance thousands of times, even though the ACL assignment was only one of a couple dozen permutations. Here’s the SQL you can use if you wish to probe:

SELECT [SiteId], [ScopeId], [RoleDefWebId], [WebId], [ScopeUrl], [Acl]
FROM [your Content DB].[dbo].[Perms] order by scopeurl
An even better query exposes the web URL

SELECT [fullurl],perms.[SiteId] , perms.[ScopeId], [RoleDefWebId], [WebId], [ScopeUrl], [Acl]
FROM [dbo].[Perms],[dbo].allwebs
where perms.WebId = allwebs.id
order by scopeurl
As an aside, you can see the number of security principals per scope, using this SQL:

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
So that closes the issue. The limit is not the unique combinations of ACLs, but instead the number of times permissions has been broken from a parent in the Document Library. So the guidelines might be:

Use folders whenever possible to encapsulate security
Break up huge Document Libraries along security boundaries
Never go past 50,000 unique permissions per list
You don’t want to go beyond 5,000 unique permissions per list without suffering severe performance impact
Don’t breech 1,000 unique permissions per list, otherwise you will see a performance impact of 20% or more
Never, ever feed a SharePoint Administrator caffeine after midnight…lol
Microsoft writes:
https://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx
The maximum number of unique security scopes set for a list should not exceed 1,000.
A scope is the security boundary for a securable object and any of its children that do not have a separate security boundary defined. A scope contains an Access Control List (ACL), but unlike NTFS ACLs, a scope can include security principals that are specific to SharePoint Server. The members of an ACL for a scope can include Windows users, user accounts other than Windows users (such as forms-based accounts), Active Directory groups, or SharePoint groups.

SharePoint 2010:
When a greater number of unique security scopes than the value of the List Query Size Threshold (default is 5,000) set to the web application are created for folders or documents in a list, there’s a significant performance degradation in SharePoint operations that badly affects end users operations(rendering content) as well as SharePoint activities like indexing SharePoint Content. After the number of unique security scopes exceeds the value of the List Query Size Threshold, SharePoint uses a code path that requires additional SQL round trips to analyze the scopes before rendering a view.