SharePoint performance and setting item permissions with a workflow

If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc.

The most natural way to tackle such request would be to have a event-handler or a workflow running when item is created or modified and modify current item's permissions. It very simple with any workflow solution - you just need to use the "set item permissions" activity.

If you deploy it on some small list (up to 50 items) with small number of users (4-5) then everything will work fine. If you try to implement it on larger list (few thousand items) with larger number of users then you may run into serious performance issues. You can experience long page load times, very high load on the SQL processor, timeouts for queries, IIS and OWS Timer unresponsiveness and workflow crashes.

The information about unique item permission limits can be found:
- here for 2007 -
- and here for 2010 - 
(look for the "Security scope") but it's worth clarifying what it exactly means.

For both SharePoint 2007 and 2010 there are limits of 1000 unique security scopes for a list. Microsoft defines a security scope as a "security boundary for a securable object and any of its children that do not have a separate security boundary defined".

In other words this is a single entry of a permission level-user(group) pair for a single item. If you do not break the inheritance for any item, the total number of scopes for the list will be equal to the number of scopes for the list itself (you can view it by going to list permissions page). If you break inheritance for 2 items then the total number of  scopes for the list will be then number of scopes for the list itself + the scopes for each item. And if you set permissions on user levels, then the list will automatically get new scopes for each user with the "Limited access" permission level, which then can be inherited by items that have their permission broken and before you know you end up with not 1000 but 50.000 security scopes.

The cause of the problem is laying in the way SharePoint tackles the permission settings.I guess that when the SharePoint team has designed this functionality they did not think that people would break the inheritance this much. Having a workflow do it automatically for you can get you very quickly past the recommended limits.

What's worth mentioning is that Microsoft states that in case of exceeding the limits the impact of the performance degradation will be the entire farm not only the list where the security scope exist! This is something I can confirm because we have found out this problem on our own while deploying a solution for our customer (This limitation was documented only in the second half of 2010) - when our farm started to run terribly slow we started looking for the cause and after analyzing all the queries and SQL tables we saw what has happened.

There is no good workaround for this problem.

One of the solutions could be using folders - you can set up permissions for few folders and then put items in those folders based on their parameters. The benefit from this solution is that permission inheritance is broken only on the folder level, so even if you have few thousand items in each folder, they all inherit permission scopes from the folder, so the number of scopes does not rise with each new item.

This solution has few downsides - you need to have a limited number of security combinations (like only A, only B, only A&B, only A&B&C, only B&C, etc), because if you would like to have a separate folders for all possible combination for 100 users then you will get nowhere. Second problem is that SharePoint does not allow you move an item into a folder - you need to create a new one in the folder and delete the existing one. And once you do it you loose your workflow, workflow history, change the item ID and link to it and will need to tackle it as well.

Other solution is to remove everyone's permission to such list and create your own web parts that will run with elevated privileges and will take care of the presentation of the list and it's items and restricting permissions. This means that SharePoint list would serve only as a database and you would need to code all other functionality (list views, display and edit forms, web services, etc) yourself.

Hope you will benefit from this Article and look forward to your comments.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.