Clarifying the Security Scope limits in SharePoint lists

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.

Additional Read

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.

Start Your SharePoint Project in a Click

Our technology and wide delivery footprint have created billions of dollars in value for clients globally and are widely recognized by industry professionals and analysts.

Let’s connect and brainstorm!