How to Change Microsoft Dataverse Table Ownership Security Models

In a previous article "A Brief Explanation of Dynamics 365 Entity Ownership Performance Considerations" I discussed some of the performance implications of user owned entities over organization owned entities. If you're not familiar with this topic I suggest you browse over that article before continuing here. The short version is the performance profile of a user/team owned entity is dramatically higher than an organization owned entity and in larger scale systems this matters. If you've been creating Dynamics 365 systems long enough you eventually run into that situation where you accidently created an entity with the wrong ownership model. The longer it takes to discover this mistake the more pain there is to change it because of ongoing development work or your worst-case scenario you've already deployed to a live production environment. For the record I realize we're now living in the Dataverse and entities are now called tables so bear with me on the naming convention. I've had enough people reach out to me about my comment about changing entity ownership that it's long overdue to complete the second part of my previous brief on entity ownership.

In the Beginning

In earlier times I didn't really have an appreciation for the entity/table type I was creating. All I knew was user/team owned entities gave me more granularity to configure security permissions. On one project I recall it was suggested we simply create all of our custom tables as user owned because they effectively could be configured as organization owned if necessary. This made sense on the surface because the team never had to be concerned about changing it after the fact. Where I began to understand the true implications of table ownership models was when I decided to go through the database layer and look at the platform security implementation. This aspect of the platform is not documented and is certainly uncommon knowledge. One thing I did know is that for any user owned table the primary performance bottleneck is the Principal Object Access table. Depending on the security model design this table can grow exponentially. The larger the system the bigger challenge this table can be and if we aren't careful a poor security model can literally eventually bring a system to its knees in a relatively short period of time.

Here is what you need to know about entity ownership. Whenever a table is created in the system you must designate the ownership model. This is irreversible once chosen. What happens behind the scenes is a series of fields and relationships are created for your table. If you choose user owned there are more relationships created to accommodate the increased security granularity of your table. An organization owned entity only performs a single table join check. A user owned table however must perform 5 table joins looking for the right permission to grant access. The last security check for user owned tables is the POA table to see if there are any exception privileges granted through a share.

Not to rub this in your face, but I want you to understand the impact querying this table has on running displaying records in a Dynamics/PowerApps application. Imagine you created a table to hold a list of 10 widget options and you've associated table to a form on another table. Now let us imagine our POA table has grown to 10 million rows. Every time the user pulls up that form the platform must query the security on that list of 10 options to determine if the user should be able to see them all. In essence the platform has to query 10 widget rows plus the 10 million POA rows. Why? Because even if a user doesn't have permissions to any of the 10 widget rows through their consolidated security roll privileges, they may have a share record that gives them permissions. If there are no security requirements around our widget table, we don't want the additional overhead of glandular security queries.

How to Update Entity Ownership

So here you are having created one or more entities either as organization owned or user owned and clearly there's no option to change it… but is there? The challenge is the platform doesn't support rearranging the table relationships and the queries generated for the chosen security setup. I don't blame the product team because that would probably be a little messy to support such a feature. What we can do is recreate the target table, but we must do it in such a way that we don't lose our customizations. I would advise you wait until after hours to perform thing operation as ongoing development could cause issues.

1. The first thing we must do is create a temporary holding solution and add our target table to it. When asked if you want to add dependencies say yes. We want to make sure we capture every customization associated with our table because we want to restore those afterwards.
2. Export your temporary holding solution as an unmanaged solution.
3. This is unfortunately the hard part. You must delete or break every dependency to your target table such that you can delete it from the system. This is the only way to undo the security configuration of the table when it was created. The ease of doing this depends on how entangled the entity is with other things so you want to make sure you add anything that is required like dependent workflows and such.
4. Recreate your table with the correct ownership model. You don't have to do anything else to it because we're going to use the solution we exported to restore its customizations.
5. Import your solution back into the system. Once done you should see all your table return to its previous state along with any other component dependencies.

Consequences and Warnings

Doing this in your lower environment s is all fine and good. The unfortunate side effect of performing this operation is your solution will now be incompatible with upper environments so you must perform the same operation everywhere else. The one environment that clearly has the most consequence is production especially if you've deployed managed solutions you're going to have to sit down and really evaluate the pros and cons of changing table ownership in a production environment. You will have to perform a data backup and restore because you're removing the table. If you have a significantly large set of data to deal with this is a larger effort. The irony is the larger the system the more significant positive performance impact it may have.

 Conclusions

I have faced this issue on multiple occasions and the first few times I had to do this operation manually. Needless to say, it wasn't fun and most often I missed restoring some customizations exactly the way there were previously. This technique provides a way to get the job done. The sooner you catch something like this the better.

If you have any thoughts or comments about this tactic, please feel free to leave a comment.