The Principal Object Access table has the most boring sounding name in Dataverse – but stick with me. If you have a large Principal Object Access or POA table it is well worth your time fixing it.
Spoiler: I managed to reduce the size of our POA table from over 100 GB to less than half a GB with no impact on users.
Table of Contents
Why care about a large POA table?
Because money doesn’t grow on trees and Dataverse storage ain’t cheap.
When I started my new role, over 100 GB of that very expensive storage was being used by the Principal Object Access table. Charities benefit from Microsoft’s not-for-profit pricing, but usually each GB of Dataverse storage retails for $480 a year. 100 GB of POA storage could cost an organisation up to $48,000 USD a year!
So, when I found out that we were using so much of it, I went on a deep dive journey into old blogs and Microsoft documentation to demystify the POA table, understand what it does, why it’s so large and how to fix it.
And unless your environment is set up perfectly, the chances are you can make some simple changes to massively reduce the POA table and reduce your costs.
What is the Principal Object Access / POA table?
The Principal Object Access (POA) table is what Dataverse uses to grant access to records beyond a user can normally see.
Every time someone tries to open a specific record, Dynamics first checks the user’s security role permissions. If access is denied based on those roles, it then checks the POA table to see if any access privileges have been granted for that user to that particular record.
For example, let’s say Adam, shares an Opportunity record with Bronte, who normally can’t see Opportunity records. When Adam shares that record, Dynamics adds a new row to the POA table mapping Bronte as having access to that specific Opportunity.
Now when Bronte later tries to view that Opportunity, Dynamics would usually deny access but when it checks the POA table, it finds the row giving Bronte access to that shared record, and allows her to view it.
The POA table essentially overrides the baseline security permissions defined in roles and provides a way to customise access on a granular record-by-record basis through manual sharing, assigning, reparenting, and other actions. Each new privilege is dutifully logged as an entry in the table.
This ability to make exceptions is powerful, but can also cause the POA to rapidly grow over time as more and more specific access rules accumulate across all environment entities and records.
Why is it so large?
Theoretically, it could be because your users love sharing records with each other.
But unless someone is spending all their time sharing records for fun, you’re not going to end up with millions of POA table rows from individual sharing.
Microsoft provide some tips on what can cause a large POA table, which really boil down to:
- Reassigned records being shared with the original owner in the organisation.
- Inefficient sharing of records by not using teams.
- Sharing more than when it’s needed.
- Poor permissions resulting in a large need for sharing.
Now, these are all possible reasons that a POA table might be large, but I think Microsoft really buried the lead on this one.
The biggest reason for POA table growth is Indirect sharing.
Indirect sharing
A major driver of POA bloat is the indirect sharing that occurs due to default parental entity relationships.
If two entities have a parental relationship, then when a child record is created for a parent record owned by a different user, Dataverse will automatically share the child record with the owner of the parent, creating a new POA row.
For example, Contacts and Activities have a parental relationship by default. When a user creates an Activity record for a Contact they don’t own, Dynamics will automatically share that Activity record with the owner of the parent Contact record by adding a new row to the POA table.
The intention is to allow the contact’s owner to see all activities related to their contact. However, Dynamics doesn’t check whether the contact’s owner already can see that particular activity – it just goes ahead and shares it anyway by creating a new POA row.
Remember – this doesn’t only apply to activity records. The same behaviour happens for all parental relationship. If the owner of the parent record is different to the owner of the child record, a new POA record will be created.
In our case, the biggest issue was activities. Some activity types are created by a data integration user, and all users could already see those records. But because the data integration didn’t own any contacts, we were racking up millions of unnecessary POA rows from the system constantly sharing activity records with users that could already see them!
So while intended as a convenience, parental relationships can cause POA table sizes to spiral out of control from cascaded permissions – often granting the same access privileges to users who already had those rights to begin with.
What’s taking up all my POA space?
Before you start making big changes, you should work out what is taking up so much of your POA space. This will help you identify which records are being automatically shared.
There’s a few ways you can do this:
Use Power BI
If you like using Power BI, like me, then I think that is the easiest way to analyse POA records. You can use the oData connector to visualise and analyse your POA records. We had nearly 100 million rows, so I took a sample of just 500,000 rows and used that to determine what was causing the large number of records.
This blog explains how to use an oData source, so if you’re unfamiliar with Power BI, you can follow those instructions. Use the URL below as the oData source:
https://<YOUR DYNAMICS ENV>.crm6.dynamics.com/api/data/v9.2/principalobjectaccessset?$count=true&$filter=inheritedaccessrightsmask+ne+0+and+accessrightsmask+eq+0&$top=500000
This query will return the rows in the Principal Object Access table as a JSON, which Power BI will kindly format into a table for you. It will return a count, which will usually just be 5000, unless you have fewer records than that. It will only include records which has been created by inherited access because of this filter inheritedaccessrightsmask+ne+0+and+accessrightsmask+eq+0.
This will also only return the first 500,000 rows – which will still take a little while. You can do more if you’d like by changing the value at the end of the URL.
By analysing the objecttypecode column you can see which entities are causing POA tables to be created. The objecttypecode represents each entity as a different number. To work out which entity corresponds to which the number use this API query:
https://<YOUR DYNAMICS ENV>.crm6.dynamics.com/api/data/v9.1/EntityDefinitions?$select=LogicalName,ObjectTypeCode
Use Microsoft’s Queries
Microsoft Support provided some useful queries in their recommend solution.
Unfortunately, they won’t work very well if you have a lot of POA records as it’ll be too much for Dataverse to aggregate:
- Get the Logical Names of the entity with the ObjectTypeCode associated with it:
- https://<YOUR_DYNAMICS_ENV>.crm6.dynamics.com/api/data/v9.1/EntityDefinitions?$select=LogicalName,ObjectTypeCode
- Get the number of records by entity for all records in the POA table:
- https://<YOUR_DYNAMICS_ENV>.crm6.dynamics.com/api/data/v9.2/principalobjectaccessset?$apply=groupby((objecttypecode),aggregate($count%20as%20count))
- Get total count by accessmask, inheritedaccessmask values for a particular entity. Change the entity name as needed.
- https://<YOUR_DYNAMICS_ENV>.crm6.dynamics.com/api/data/v9.2/principalobjectaccessset?$apply=filter(objecttypecode+eq+%27account%27)/groupby((accessrightsmask,inheritedaccessrightsmask),aggregate($count+as+count))
Use XRM Toolbox
There’s an XRM Toolbox tool called Search data in PrincipalObjectAccess (POA), but I didn’t end up using it. You could use that to analyse your records instead.
What is the Access Mask?
When you’re analysing the POA table, you’ll want to see what’s going on in the accessrightsmask and inheritedaccessrightsmask columns.
These two columns store the information that tells Dynamics whether someone can see something or not.
For each row, if the value in inheritedaccessrightsmask isn’t zero, that means it was created automatically. If the value in accessrightsmask isn’t zero, that means it was created by a user sharing the record with another user.
Later, if you reset some POA rows, this will change the value in the inheritedaccessrightsmask column to zero.
You can see what each access rights mask number means in this Microsoft article.
How do I fix it?
So hopefully you’ve worked out which of your tables is creating inherited access rows. Here’s how we can stop creating those access rows:
1. Work out the user impact of removing inherited access
Start by determining what will happen if you do stop the automatic inherited access for an entity. This will mean that users of the parent record won’t be able to see child records unless the security role already permits them to.
So if a custom kind of activity is causing lots of inherited access records because the owner of that record is often different from the owner of the contact the activity is related to, you should determine what impact it would have if the parent couldn’t see the record.
It may be that it would make no difference at all! If your security roles let everyone can see the relevant child records in their business unit or team, then this won’t make a difference at all.
But it could also mean that suddenly people won’t be able to see records that they should, so it’s important that you work it out.
By the end of this step you should know which entities and relationships you want to stop creating inherited access rows for.
2. Change the Relationship behaviour
For every Parent/Child relationships that should no longer create inherited access rows, you need to change the behaviour of that relationship from Parental to Custom.
To do this, open the child entity in Power Apps, and select Relationships. There you should see a Many to One relationship with the parent table that you want to change. Click on it to open it.
You should see that the Type of behaviour at the bottom is Parental. This is what needs to change.
To make it stop creating inherited access rows on creation, all you need to do is change it to Custom and then change Reparent to Cascade None. The other relationship behaviours are up to you, but Parental records have Cascade All for everything else by default.
That should stop new records being created, and theoretically run a job to delete all the old records.
Unfortunately, in our case, it didn’t delete the old records, so we had to do that manually using the steps below.
How do I delete POA rows?
You can’t delete POA rows directly, but if you want to delete inherited access rows, there is a useful process to do this.
It works in two steps:
- You specify which POA records to reset the inherited access mask to zero
- The regular automated delete job will go and delete them
A word of warning – resetting the inherited access can cause the space taken up by inherited access to actual grow in the short term. We saw a long delay between resetting the inherited access, deletions and actual drops in the size of our POA table. This may temporarily increase the size of your database.
In the long run it’s well worth it. We had 100 GB of POA space, this then increased to nearly 120 at its peak of resetting, but then got reduced massively down to less than half a GB now.
1. Get a baseline
It’s useful to know how many POA records you had to begin with so you can keep track of whether they are being deleted or not. The best way to do this is to use the Retrieve Total Record Count API function, which updates every 24 hours.
https://<YOUR_ DYNAMICS_ENV>.crm6.dynamics.com/api/data/v9.1/RetrieveTotalRecordCount(EntityNames=[‘principalobjectaccess’])
Write that number down somewhere as it can take a good while for all the rows to get deleted.
2. Work out what rows to delete
To delete the rows, we need to use the Reset Inherited Access API endpoint.
This is a really simple endpoint that takes a filtering parameter and will run over all the rows that meet that filtering criteria and reset their inherited access. Once reset, they get cleaned up and deleted later.
This means you need to think about what rows you want to reset and make sure you only reset those rows. You can choose which rows to filter on by using any of the filters on these columns.
3. Write the Fetch XML
Now you know which rows you want to reset, you need to write the Fetch XML to do that.
You’ll need the object type code for the table you want to reset access for. This is a number that represents the entity. You can get this using this query:
https://<YOUR_DYNAMICS_ENV>.crm6.dynamics.com/api/data/v9.1/EntityDefinitions?$select=LogicalName,ObjectTypeCode
By default, 4200 is the object type code for activitypointer, which is what is used in the examples below. Annoyingly, you can’t filter on particular types of activities – it’s all activities or none.
Here’s a few example FetchXML queries you can alter to fit your needs:
Reset all inherited access rows for Activities
‘<fetch><entityname=”principalobjectaccess”><attribute name=”principalobjectaccessid”/><filter type=”and”><condition attribute=”objecttypecode” operator=”eq” value=”4200″/></filter></entity></fetch>’
Reset inherited access rows for Activities created by a specific user
‘<fetch><entityname=”principalobjectaccess”><attribute name=”principalobjectaccessid”/><filter type=”and”><condition attribute=”objecttypecode” operator=”eq” value=”4200″/>”><condition attribute=”principalid” operator=”eq” value=”00000000-0000-0000-0000-000000000000″/></filter></entity></fetch>’
If you need more help you can check out this article.
Once you’ve written the FetchXML and you’re certain that you’re ready to use it, you can move onto the next step.
4. Reset the rows returned by the Fetch XML
To reset the inherited access rows, just run this in your browser.
https://<YOUR_DYNAMICS_ENV>.crm6.dynamics.com/api/data/v9.0/ResetInheritedAccess(FetchXml=@fetchXml)?@fetchXml='<fetch><entityname=”principalobjectaccess”><attribute name=”principalobjectaccessid”/><filter type=”and”><condition attribute=”objecttypecode” operator=”eq” value=”4200″/>”><condition attribute=”principalid” operator=”eq” value=”00000000-0000-0000-0000-000000000000″/></filter></entity></fetch>’
Microsoft forget to use single quotes in their example. You’ll need to use single quotes ( ‘ ‘ ) around the fetch XML otherwise it won’t work.
This will return a message saying that it is either being executed Synchronously or Asynchronously, depending on how many rows need to be reset.
5. Check Progress in Advanced Settings
You can check the progress of the reset job by checking out Advanced Settings. Click on the Setting icon in Power Apps and select Advanced Settings.
Open System Settings > System > System Jobs.
There you should be able to see the Revoke Inherited Access job. This will update to Succeeded when it has completed.
6. Wait for the delete job to run
Remember, the Reset Inherited Access API doesn’t actually delete the POA rows – it simply resets the InheritedAccessMask value to 0 for the filtered rows. The heavy lifting of purging those reset entries is handled by the asynchronous deletion service jobs.
After the POA rows are set to 0 they will eventually get deleted. This can take a while.
How quickly those stale POA rows get cleaned up depends on two environment database settings that control the deletion cadence:
- ExpireChangeTrackingInDays – This determines the maximum number of days the system will retain reset POA rows before deleting them.
- ExpireSubscriptionsInDays – In addition to change tracking rows, this setting governs how long the system waits before removing any inactive subscription entries.
The default values for these settings are 30 and 90 days respectively. To accelerate the POA cleanup process after a reset, you can temporarily lower these values. We had already lowered them, so I left them as is.
Summary
I admit, the Principal Object Access table is one of the more boring topics of the Dynamics/Power Platform architecture. But neglecting this table can have very un-boring consequences on your storage size and your billing.
In summary, the POA table stores and governs access privileges across all your data entities. Left unchecked, it can bloat rapidly from accumulating millions of inherited access entries, burning through storage space. However, with a few changes to relationships, you can keep the POA lean and your database sizes reasonable.
It may not be the most riveting table, but giving it a little TLC can prevent some major billing headaches down the road.