My PrincipalObjectAccess table is corrupted. I cannot restore to a backup. I've used DBCC CHECKDB and every other possible way to attempt to retrieve/repair data.
I made a copy of the current corrupted database and then dropped and recreated the PrincipalObjectAccess table. This corrected all the corruption - which appears isolated to this table. Is there a way for me to do this on the production box and not entirely screw my installation?
It seems to me that the PrincipalObjectAccess table already isn't working - b/c it is corrupted...well, at least it can't access some of the records - but Dynamics is still working at this point - so what happens if I drop and recreate?
- Proposed as answer by JLattimerMVP, Moderator Saturday, January 26, 2013 3:18 AM
+1 this is (as I am sure you know) definitely unsupported and very possibly a short route to a dead CRM system.
Impact would be that all records of all types previously available to users due to sharing, or "reparent" cascading behaviour would not be. It might be hard to near-impossible to determine if this is currently vital to your business functionality or irrelevant - a user will often not know why or how they have access to a record, just that it works as they want, need and expect.
The POA seems most likely to have become corrupt due to other changes being made directly to the database, to change user records, or insert / update others. Have any other direct SQL changes been made, to your knowledge?
Opening a ticket to help you fix the corruption may be your best option. Opening a ticket after you drop and recreate the POA table is almost certain to require yo to pay for such support, if indeed it is made available to you at all.
Hope this helps.
Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
Blog: Getting IT Right
As it already mentioned that any changes directly to the database may break the CRM system (and it is unsupported).
If still you are ready to take risk then you can try below approach.
Do not drop the PrincipalObjectAccess table, it may cause many problems, Instead you can truncate the table.
As already you have the backup of PrincipalObjectAccess table, Try below SQL query to insert the records in PrincipalObjectAccess table from your backup table (i assumed the table name as backupTable).
Declare @SystemUser nvarchar(100) Declare @ObjectId nvarchar(100) DECLARE @ObjectTypeCode INT DECLARE @UserType INT DECLARE @Accessmask INT DECLARE @InheritedAccessmask INT declare Cur_SHAREWITHUSER cursor for Select ObjectId,Principalid, ObjectTypeCode,PrincipalTypeCode,AccessRightsMask,InheritedAccessRightsMask from backupTable -- (CHange the backup table name) where AccessRightsMask>0 OR InheritedAccessRightsMask>0 open Cur_SHAREWITHUSER fetch Cur_SHAREWITHUSER into @ObjectId,@SystemUser,@ObjectTypeCode, @UserType,@Accessmask, @InheritedAccessmask while(@@fetch_status=0) BEGIN INSERT INTO PrincipalObjectAccess (Principalid,ObjectId,ObjectTypeCode,[PrincipalTypeCode],[AccessRightsMask],ChangedOn, [InheritedAccessRightsMask] ,PrincipalObjectAccessId ) values (@SystemUser , @ObjectId ,@ObjectTypeCode,@UserType,@Accessmask,GETDATE(),@InheritedAccessmask,NEWID()) fetch Cur_SHAREWITHUSER into @ObjectId,@SystemUser,@ObjectTypeCode, @UserType,@Accessmask, @InheritedAccessmask END close Cur_SHAREWITHUSER deallocate Cur_SHAREWITHUSER
Chandan - http://mscrm-chandan.blogspot.in/ I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!
- Proposed as answer by Chandan kumar Choudhary Friday, February 01, 2013 4:58 AM