Does Microsoft have guidance about how much sharing is too much? — CRMTOD reader

I find it hard to believe we have gone 968 tips without talking about the risks of excessive sharing in Dynamics 365. Few features in CRM parallel sharing on the “this is the best thing ever/this is my worst nightmare” scale.

How record based security can be used to control access to records in Microsoft Dynamics 365.

The good

Sharing is a good feature, because it gives administrators and users with the appropriate permission the ability to grant specific permissions to specific records, and is useful for handling exceptions to the rule. Need to have salesperson 2 handle salesperson 1’s accounts while she is out for a month filming Survivor season 83? Sharing can do that. Sharing can also be automated, meaning that if you have a need for a specific condition to automatically share records with a user or team, simple plugins, workflow assemblies, or Scribe can be used to do that. This has been the answer to many Dynamics clients funky security requirements.

The bad

While a very useful feature, sharing has a dark side.

• Performance: sharing is facilitated by the Principal Object Access (POA) table. When you share a record with a user or team, a record is created in the POA table containing the ID of the user, the ID of the record, and the permission that he or she should have. But that’s not all! The cascading nature of sharing means that if a parental or configurable cascading relationship exists that is sharing enabled, the child records in those relationships will also be shared with the user or team (and additional records will be added to POA). There is also a bunch of murky reparent/inherited share scenarios that can also create records, which can cause the POA table to grow quickly.  This becomes a performance issue when the table gets extremely large (somewhere between 20 million and 2 billion records). When you query CRM, such as opening a view, advanced find, or viewing a chart, the results are filtered by the POA table. If the table is quite large or indexes are not optimized, this can lead to very slow system performance.
• Administrator’s nightmare: Quick–show me which records are shared with Bob. You can’t do it. While you can click on a record and show who it is directly shared with, there is no way to easily do that for all records. Also, cascading/inherited shares don’t show in the sharing dialog on the record. Without opening each record in the context of that user, it is virtually iimpossible to know if your sharing strategy is working correctly
• Forgotten shares: Remember that sales rep you shared the records with while her buddy was off for a month? Odds are you will forget to unshare those records. Got a workflow that automatically shares record with Tim if they are in Seskatchewan and the plumbing industry? Well Tim moved to a different industry vertical last month. Did your workflow remember to unshare all of those records?
• Can’t “make it right”: After you use the system for a year or two, you may find that things get a bit off or you decide to make a wholesale change to your sharing strategy, so you want to run a “make it right” batch job to set/update all records with the appropriate sharing permission. There is no easy way to do this with sharing.

The answer

So to answer the question, yes, Microsoft does offer some guidance (if you are on premises) to optimizing and controlling growth of your POA table. But probably the best guide to understanding the POA table comes from this classic post from Scott Sewell. In it he explains how to decode the structure of the POA table and understand how your sharing strategy will impact database size. He also offers a Excel based decoder tool to encode/decode the POA table. Unfortunately the link in that post to the secret decoder ring is no longer valid, but Scott has located that file and you can download it here.

So now that you understand how the POA works, what are some steps that you can take to avoid the dark side of sharing?

• Team ownership: In the old days, teams couldn’t own records, so we had to share to grant multiple people access to a record, without granting access to the entire business unit. With team ownership, you can assign records to teams of users in multiple business units.
• Share with teams, not users. If you share a record with 10 users, 10 POA records are created, X 10 POA records for each child cascading shared record. If you share the record with a team with ten users, only one POA record is created (along with 1 POA record for each cascading share). This will dramatically reduce the size of your POA table. Want to take away a user’s permissions? Remove them from the team.
• Use access teams for controlled sharing. So you can’t do owner teams, but you still want to be able to grant ad-hoc access to records to specific users. Some you want to just read the record, some you want read/write. Access teams can handle that, and you can have multiple access team profiles, one for read, one for read/write. Access teams are designed with performance in mind, so they don’t actually create the team and share the record until you add the first member of the team.

The real beauty of the team approach, be it owner or access team, is that it makes it much easier to see what records a user has, just by seeing what teams he or she is a member of. If you want to run a “make it right” batch job to reset all sharing permissions, you can do that by wiping out your team membership and then running a SSIS or Scribe job to populate the teams based on the new logic.

So please share your toys and Dynamics record access, but do it wisely.