1

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 !

2

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
  1. 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
  1. 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
  1. If the Return Value equals 0, everything went well. Your item should be repaired and inherit its permissions from its parent.
1

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.

  • Thanks for your answer Thriggle. Unfortunatelly I can't try it since my problem is resolved. But I'm curious, do you know if Repair($true) is OK with Microsoft ? It's object model accessing, so I'd say it is, but there's something close in the forbidden operations list : "Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)" link – Berthim May 4 '16 at 15:30
  • 1
    Yep, I'm certain it's supported. The forbidden operation is referring specifically to a SQL command, DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option on that command is unsupported because threatens referential integrity. SPContentDatabase.Repair(true) will delete orphaned objects, but won't jeopardize the database's referential integrity. – Thriggle May 4 '16 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.