POA: What it looks like on the SQL side

By | November 24, 2017

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:

image

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:image
  • 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!

2 thoughts on “POA: What it looks like on the SQL side

  1. Kenneth Leong

    access teams really does help in performance.. but we ran into an issue with cascading sharing. So we got access teams working with a parent entity, and we set up a cascade sharing to “propagate” this to some child entities. The issue we’re running into, is after a user is added to the access team of the parent record, he gains access to the parent record fine. But for “some” related entities, the record remains readonly, and the user cannot delete.. and only for some record of that child entities. We checked the access team template, the delete is checked. Any idea?

    Reply
    1. Alex Shlega Post author

      Did you try Access Checker plugin for XrmToolBox to see what permissions do the users have for one of those records? Wondering if it’ll recognize the permissions(if not, then something is missing.. if yes, could be something about how CRM is interpreting the permissions.. maybe a rule on the delete button?) Also, do those users have at least owner-level “delete” permission on the entity?

      Reply

Leave a Reply to Alex Shlega Cancel reply

Your email address will not be published. Required fields are marked *