Repair/delete list item with corrupted permissions

Question

I've been a SE read-only user for several years, but today I'm facing a problem I can't resolve nor find any help so I created my account. (... well actually I created it to answer to a since on-hold Star Wars question, but that's off-topic...)

So !

I have a SPListItem which had its permissions programmatically updated. For an unknown reason, this update failed (really badly) and its permissions are now corrupted. Here are the symptoms (with sitecoll + farm admin account) :

• Checking permissions via GUI throws the error : Cannot complete this action. Please try again.
• Checking permissions (RoleAssignments) via PowerShell returns null.
• $item.ResetRoleInheritance() throws "Cannot complete this action..."
• Editing/Deleting the item via GUI throws an access denied.
• Editing/Deleting the item via PowerShell throws a 0x80070005, which is also an access denied.

Actually, my problem is exactly as described in this article. Unfortunately, the author's solution is to run a stored procedure, which AFAIK, is forbidden by Microsoft as any direct SQL operation in SharePoint.

Does anyone know a way to run this store procedure "legally" (= through GUI or object model) or any other way to repair or delete this item ?

Thank you for your help.

PS : BTW, if you know what could have been the cause of this failure, I'm interested too thanks !

Answer 1

After further search, we found out that some stored procedures might be used with Microsoft support.

Examples of unsupported database changes include, but are not limited to, the following: [...] Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation

And the proc_SecResetItemPerm is documented in this protocol.

So we eventually used it and it worked like a charm.

For the record : Solution found at http://sharepointbitsandbytes.com/2013/03/sharepoint-2010-item-level-permissions/

1. Perform a full backup of the involved Content DB.

2. With PowerShell, get the following informations

$web.Site.ID   # for SiteId
$web.ID        # for WebId
$item.Url      # for Url (Warning: you'll get site-relative url, but you'll need webapp-relative url after)
$item.UniqueId # for DocId

3. Get the OldScopeId by querying the involved Content DB

USE [ContentDB_Name]
GO

SELECT TOP 1 [ScopeId]
FROM [ContentDB_Name].[dbo].[Perms]
WHERE ScopeUrl = 'path/site/Lists/mylist/1234_.000'
GO

4. Reset your item permissions by using the stored procedure

USE [ContentDB_Name]
GO

DECLARE @return_value int,
@NewScopeId uniqueidentifier,
@RequestGuid uniqueidentifier

EXEC @return_value = [dbo].[proc_SecResetItemPerm]
@SiteId = 'ba1c5347-4bb8-443e-a8db-ce60020915d9',
@WebId = '34c030a2-0963-48cc-bf97-d9548d6b5a34',
@OldScopeId = '01bd0749-8ecc-4508-8b47-41b11f039091',
@Url = N'path/site/Lists/mylist/1234_.000',
@DocId = '961b68fa-dbd5-41b4-a098-b53a33b9e550',
@NewScopeId = @NewScopeId OUTPUT,
@RequestGuid = @RequestGuid OUTPUT

SELECT @NewScopeId as N'@NewScopeId',
@RequestGuid as N'@RequestGuid'

SELECT 'Return Value' = @return_value
GO

5. If the Return Value equals 0, everything went well. Your item should be repaired and inherit its permissions from its parent.

Answer 2

Consider using the Repair method available on SPContentDatabase objects, documented by Microsoft here.

You can invoke this from Powershell (once the SharePoint Powershell snapin is loaded) like so:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$databases = Get-SPContentDatabase
$databases | %{ $_.Repair($false) }

If you're curious about the results of the Repair calls, you can store the output string in a variable and optionally convert it to an XML object or save it to a file like so.

$databases = Get-SPContentDatabase
[xml]$xml = "<results></results>"
$databases | %{ 
    $result = $xml.createElement("result")
    $result.innerXml = $_.Repair($false) 
    $xml.SelectSingleNode("results").appendChild($result)
}
$xml.OuterXml > "Results.xml"

Note that passing $true to Repair will give it permission to simply delete the corrupted objects instead of trying to fix them. In some cases, that may be the best fix.