Dropping and Recreating PrincipalObjectAccess table.


  • 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?


    Friday, January 25, 2013 4:01 PM

All replies

  • I'm pretty sure that would be a bad idea (any changes directly to the database would be considered unsupported).

    If restoring from a backup isn't an option, you may want to open a ticket with Microsoft.

    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    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

    Sunday, January 27, 2013 12:11 PM
  • Hi,

    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
    fetch Cur_SHAREWITHUSER  into @ObjectId,@SystemUser,@ObjectTypeCode, @UserType,@Accessmask, @InheritedAccessmask
    		INSERT INTO PrincipalObjectAccess  
    		[InheritedAccessRightsMask] ,PrincipalObjectAccessId )
    		values (@SystemUser , @ObjectId ,@ObjectTypeCode,@UserType,@Accessmask,GETDATE(),@InheritedAccessmask,NEWID())
    	fetch Cur_SHAREWITHUSER  into @ObjectId,@SystemUser,@ObjectTypeCode, @UserType,@Accessmask, @InheritedAccessmask
    close Cur_SHAREWITHUSER 
    deallocate Cur_SHAREWITHUSER 

    Chandan - I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!

    Monday, January 28, 2013 5:14 AM