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 – never mess with a SharePoint database directly.

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 best practices and recommendations. The magic number of 5.000 was left far behind – after half year of usage some lists already had over 25.000 items with item level permissions. Almost every action (opening an item, saving it etc.) took from 3 to 10 seconds.

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:

Cannot complete this action

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:

HasUniqueRoleAssignments True
EffectiveBasePermissions FullMask
FirstUniqueAncestorSecurableObject Microsoft.SharePoint.SPListItem
ReusableAcl Microsoft.SharePoint.SPReusableAcl
RoleAssignments {Microsoft.SharePoint.SPRoleAssignment, Microsoft.SharePoint.SPRoleAssignment…}
AllRolesForCurrentUser {Full Control, Read, Limited Access, View Only…}
FirstUniqueAncestor Microsoft.SharePoint.SPListItem

but instead our items looked like this:

EffectiveBasePermissions FullMask

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:

Add-PSSnapin Microsoft.Sharepoint.Powershell
$site = Get-SPSite http://site

$web = Get-SPWeb http://site/web

$list = $web.Lists[“List”]
$item = $list.GetItemById(16855)


Now the last parameter that we need is OldScopeId. The way to get it is by querying Perms table:

USE [WSS_Content]

SELECT TOP 1 [ScopeId]
FROM [WSS_Content].[dbo].[Perms]
WHERE ScopeUrl = ‘web/list/16855_.000’

Since we have all the parameters we can execute proc_SecResetItemPerm stored procedure. We do this by writing a query against WSS_Content database:

USE [WSS_Content]

GODECLARE @return_value int,
@NewScopeId uniqueidentifier,
@RequestGuid uniqueidentifier

EXEC @return_value = [dbo].[proc_SecResetItemPerm]
@SiteId = ‘9f07b6ef-25e1-4a7a-b06e-f60019e20255’,
@WebId = ‘c3536be5-a419-4c27-88fd-269316c18757’,
@OldScopeId = ‘B2D7B4C0-8E02-4E5F-A611-020BE5770A8F’,
@Url = N’web/list/16855_.000′,
@DocId = ‘fab7dc68-50ad-4ea0-9c99-230dfdbc0567′,
@NewScopeId = @NewScopeId OUTPUT,
@RequestGuid = @RequestGuid OUTPUT

SELECT @NewScopeId as N’@NewScopeId’,
@RequestGuid as N’@RequestGuid’

SELECT ‘Return Value’ = @return_value

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!