POA: What it looks like on the SQL side

POA table is well known for its ability to cause performance issues, so, even though we can’t avoid record sharing, it’s always been recommended to sort of keep record sharing at bay by reducing the number of records in the POA.

This is where Access Teams come into play. Sure we can use Owner Teams as well, but there is an important difference between those two types of teams – Access Teams do not affect security cache on the server, so they are ideally suited for record sharing (I’d say that’s exactly why they were introduced):

http://www.itaintboring.com/dynamics-crm/access-teams-why-do-we-really-need-them/

How does it work behind the scene, though?

It’s really all about this part of any filtered view query:

This particular example is for the account entity, but you will find exactly the same query in any other filtered view for other entities.

Here is what’s happening there:

• SystemUserPrincipals is a table that lists all the objects a user can be granted access through. Not sure if what I just wrote makes sense.. But imagine a user who is also a member of various teams. That user can be granted access individually or through the team membership, and SystemUserPrincipals is the table that, for each User, maintains all those additional “user principals” in a single table. For example:
• So, if you look at both of these screenshots, it should probably make sense that, in order to determine if a user has been granted access to a specific Dynamics record through sharing, Filtered View is joining 3 tables:
  – Entity table (Account in this example)
  – SystemUserPrincipals table (to determine all the id-s through which the access could be granted)
  – PrincipalObjectAccess table (to actually check the access)

What’s interesting about that is:

• Imagine we have 10 users and we share a record with those users. That’s 10 records in the POA. Now what if we move those 10 users into a team and share the same record with that team instead? That’s only 1 record in the POA.
• What if we create 10 teams instead of one and add every user to those teams? It seems SQL would have to look at about the same number of records to verify access (10 teams, 1 POA this time)
• What if we reduce the number of POA records by 50%? Let’s say there was 1 million records, and we make it 500K. And, then, what if every user in the system gets added to a couple of new teams? It seems that the query in the Filtered View will have to look at 500K POA records for the user, that user’s default team, and those 2 additional teams now. So, before any optimizations, it would be the same number of records.

In other words, there are, actually, two tables participating in that query, and the total number of records SQL may have to look at depends on the number of records not only in the POA, but, also, in the SystemUserPrincipals. The number of records in that last table depends on the team membership – as we keep creating teams and adding users to those teams, SystemUserPrincipals keeps growing.

Which means that, although it’s a good idea to reduce the POA, it’s not necessarily going to help if we go heavy on the teams/team membership, and that’s just something to keep in mind..

Happy 365-ing!