I would say last week as CRM database cleanup week, where I was working on cleaning up the PrincipalObjectAccess with one of my customers.
Moving ahead after cleaning up the WorkFlowLog entries from POA table which were created due to Dialogs (please refer my previous article for more information on that: http://blogs.msdn.com/b/ritesh_ranjan/archive/2015/02/09/another-experience-with-cleaning-up-the-principalobjectaccess-table-dialogs.aspx ), now I am looking at cleaning up the POA table for the records which are with the value 135069719 for InheritedAccessRightsMask column and value 0 for AccessRightsMask. Don’t be mistaken, it should be the value 135069719 and 0 for the same record under associated mentioned columns.
Let me explain it further for your better understanding of the scenario:
On a daily basis we were noticing hundred thousands of records in the POA table for the ActivityPointer, SalesOrder, Anotation and lots of other entities. After drilling down further on what is making these entries in the table we found that all of these entries were based on the Reparent attribute set to Cascade All. As Jon clearly mentioned in his blog (http://blogs.msdn.com/b/crminthefield/archive/2010/08/16/excessive-principalobjectaccess-poa-table-growth-in-crm-4-0.aspx) that with this setting, sub records would be shared to the owner of the parent record. For example: Let’s say that User1 owns Account1. User2 has access to Account1 and creates a case underneath Account1. With the out of the box Reparent options, a record would be created in the POA table that would give User1 access to the newly created case.
After my analysis I understood that these records will have a value (135069719) which decodes to all privileges except ‘Create’ under the column InheritedAccessRightsMask. In order to control the behavior first we really need to understand and explain the behavior, so that we could understand the pros & cons of changing the sharing behavior from Cascade All to Cascade None, which will completely stop those entries into the POA table. For better understanding on this change you can refer Jon’s blog mentioned above.
By now you would understand the reason behind these entries into the PrincipalObjectAccess table but at the same time you will have a question in mind, i.e. How to clean up existing data which is already created in POA because of cascade share all, right?
Try out with this simple test (In my case it was Account entity and Phone Call activity which was filling up POA table)
– Assign Account from User 1 to User 3
– Then assign Account from User 3 back to User 1
– Assign PhoneCall (which was under that account record) from User 2 to User 3
– Then assign PhoneCall from User 3 back to User 2
This sets the value of InheritedAccessRightsMask to 0, which means no privileges and this also marks the record to be deleted by the deletion service. Now this was good for testing with a single record but what if you have millions of Accounts and you need to do this ownership reassignment for all of them?
The supported way is definitely to do it through SDK, creating a dummy user 3 and assign all the records to that user and then from that user3 assign it back to the user1 and user2. This could be done as a bulk operation using CRM SDK and this would help you clean the PrincipalObjectAccess table up to a great extent. In this case it reduced the table size by 80%, yes you read it right, 80%.
Hope this helps.
Stay tuned and happy reading!