Permissions play an important role in SharePoint. Today in my first blog post I’d like to share a solution to a rare exception when SharePoint fails to assign permissions and you are left with an item that you can’t even delete. But first some background on the project which made me break the first rule of SharePoint –
The project that I finished working on half a year ago is a business process management system. Lots of lists, lots of libraries, over 30.000 sites (still counting) and of course the biggest pain for users (performance) and support team (issues) – item level permissions. These are assigned using event receivers (ItemAdded and ItemUpdated) based on a Department site column which was used in every site content type. This wouldn’t be an issue if we had kept everything according to Microsoft’s
It was slow, but it worked until we hit about 15.000 items in a single list. Then every once in a while (1 in a 1000) an item was created without any permissions – we called them orphans. Too bad the exception was random and we couldn’t reproduce it. ULS logs would show this error:
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint ‘Perms_PK’. Cannot insert duplicate key in object ‘dbo.Perms’. The duplicate key value is (9f07b6ef-25e1-4a7a-b06e-f60019e20255, 0x, ok/objektai/16855_.000). The statement has been terminated.
Users (except for site collection administrators) weren’t able to see these items in lists. They could open view or edit forms but couldn’t do anything that called an SPListItem.Update() or SPListItem.Delete(). Trying to view item permissions would result in an error:
Luckily the URL contained ID of the SPListItem so we were able to check the properties of that item using PowerShell.
Usually items with unique permissions look like this:
but instead our items looked like this:
Yes, all of these blank values were nulls. There were no possible ways of fixing these orphans either by code or using PowerShell – we couldn’t delete them (UnauthorizedAccessException) and SPListItem.ResetRoleInheritance() was returning an error System.Runtime.InteropServices.COMException (0x80004005): Cannot complete this action. The only way to do ResetRoleInheritance was to use a SharePoint database stored procedure proc_SecResetItemPerm. It takes SiteId, WebId, OldScopeId, Item Url and DocId as parameters so first we need to get them. PowerShell works great for that. Here is an example script with it’s output:
Now the last parameter that we need is OldScopeId. The way to get it is by querying Perms table:
Since we have all the parameters we can execute proc_SecResetItemPerm stored procedure. We do this by writing a query against WSS_Content database:
The query returns NewScopeId and we end up with broken role inheritance and no new roles assigned. Now we can assign new permissions or delete that item.
Hope it helps!