Elegant Workaround for SharePoint Security Threshold

Hi!
We are currently designing a solution for a customer where there will be lots of documents with broken inheritance in SharePoint which is synced with Dynamics CRM. 
As we know, in SharePoint 50.000 items can have broken inheritance in a library. 
Our customer will reach that limit in a couple of years.
What is the best practice for handling this SharePoint permission scope threshold? Using folders to store the documents and set unique permissions on the folder or creating more libraries? Is there any automatic solution? 

Thanks,  
Andy

This query from one of Microsoft community pages resonates with many SharePoint admins. No matter how big security scope in SP can be, it is exhaustible. For large organizations, it is a matter of two-three years for SharePoint until the limit is over. Obviously, some comprehensive planning is needed.  In this article, we will analyze this threshold and possible workarounds to ensure consistent security of documents in Dynamics CRM-SharePoint pair.

Over years, Dynamics CRM and SharePoint synched pair has become an integral part of managing customers. It is a well-thought combination which adds great functionality to CRM.  

When you use SharePoint Online with Dynamics 365, you have the following benefits: 

• Much more functionality in managing customer-related documents 
• Possibility to share documents with non-CRM users or externally (for example, for contract changes) 
• Bigger and cheaper data storage (SharePoint storage: 10 GB standard ($0.20 per GB extra) compared to Dynamics CRM storage: 5 GB Standard ($10 per GB extra)) 
• Adding SharePoint specific options (document check out/check in and version history; interface to support OneNote integration with CRM; syncing with Desktop Library and Outlook; integration with Microsoft Delve for advanced searching functionality through document library and documents). 

All in all, this combination enables companies to hunt opportunities and extend business more effectively. But it has some limitations.

Companies working with Dynamics CRM synced with SharePoint need to remember two things.  

Firstly, this pair misses automatic synchronization of permissions and privileges. So, unless you set permissions manually, such sensitive data as contract values, signatures, prospects can slip away to unauthorized persons in SharePoint. 

And secondly, the abovementioned limits of SharePoint unique permission scopes.  

For SP 2013, 2016 and 2019, the threshold is 50 000 permissions per document library. After reaching this line, permissions can’t be assigned anymore. This supply normally exhausts within a couple of years for large organizations. However, if you follow Microsoft’s recommendations, it will happen even faster. “For most farms, we recommend that you consider lowering this limit to 5,000 unique scopes. (…) When the number of unique security scopes for a list exceeds the value of the list view threshold (set by default at 5,000 list items), additional SQL Server round trips take place when the list is viewed, which can adversely affect list view performance”, it informs.  

Probably, this feature triggered the decision to allow only 5 000 unique scopes for SharePoint online. 

And because protecting sensitive data is critical for any organization, proper planning needs to take place at early stage of implementation. 

The first issue – missing automatic synchronization between permissions in Dynamics CRM and SharePoint – can be solved by CB Permission Replicator. This product by Connecting Software is the only out-of-the-box solution in the world which covers this missing synchronization automatically. It has gained quite a lot of popularity and trust over the past few years among both private and public entities. 

The second issue – the threshold for SharePoint unique permissions – is a bit more complicated. 

For example, Microsoft doesn’t provide any easy solution to this challenge. The recommendations go along the lines of  

• either minimizing the use of unique permissions or  
• setting permissions on a complete list or folder rather than individual items or  
• reconsidering the library design altogether.  

Of course, this arrangement is possible, but it requires customization and coding.  

Besides, SharePoint permissions are not granular enough. If an employee needs to break permissions to specific documents, they require Add/Edit rights on the whole document library in the out-of-the-box integration between Dynamics CRM and SharePoint. This means, the person may get unauthorized access to some documents.  

Thanks to previous work on the CB Dynamics CRM – SharePoint Permission Replicator, we’ve learned both systems in and out. And understood that limited unique permission scopes in SharePoint creates a serious challenge for many system administrators.  

SharePoint Structure Creator is our response to the issue. It automatically creates or selects document libraries in SharePoint based on defined rules to avoid reaching unique permission scope limitations. 

It is actually quite smart. SP Structure Creator will put documents into distribute document libraries which are created according to the rules you define: 

• Based on date (Yearly, Quarterly, Monthly, Weekly, Daily or custom rule)  
• Based on starting character(s) of record name  
• Based on starting character(s) of record ID
• Document library per record.  

Thus, you don’t need to group documents with broken permissions in special folders or recreate the SharePoint structure completely. You only need to choose the rule and make sure it will be sufficient not to hit the 5k /50k limit.  

A large company is planning to have around 1 million records (5 different entities) in Dynamics 365 with documents stored in SharePoint. Not instantaneously, but they believe the number will be reached within one and a half-two years.  

Now some technical detail. The company is operating under a flat structure. Besides, the system administrated has completely locked-down SharePoint: only Dynamics 365 users can access it, and only those documents to which they have permission. The security part is handled by CB Replicator.  

What can the administrator do in this case to avoid hitting 50k unique permission scopes? 

They apply SharePoint Structure Creator feature. It automatically creates folders and valid document locations in SharePoint once the user clicks “Documents” in D365. The admin has decided to build document libraries based on the week of entry. Each week new library is created. Thus, they ensure that the threshold is never reached. And for the user, the experience is the same. 

The feature is a CRM plugin communicating with SharePoint via REST. The plugin replaces the original implementation and behaves in the same manner. It is fully transparent to the end user – meaning the experience remains the same. So, when the user clicks ‘Documents’ tab in Dynamics CRM the grid with documents appears within a few seconds.  

A SharePoint folder is created under a privileged user, so the calling user does not need to have any rights in SharePoint. Once a SharePoint object is created it breaks permission inheritance and grants access for the calling user – so they can start uploading document immediately.   

Supported systems:  

• CRM 2011, CRM 2013, CRM 2015, CRM 2016, Dynamics 365, Dynamics 365 Online  
• SharePoint 2013, SharePoint 2016, SharePoint 2019, SharePoint Online  

This plugin is indeed a clever solution to the SharePoint limitation. Best of all, it is automatic – tested, proven it works and protecting from human factor.  

Read other stories about Dynamics CRM and SharePoint integration:

Replicate the Dynamics CRM security model to SharePoint: Updated

Bridging the security gap between Dynamics 365 Privileges and SharePoint Permissions