Tip #1061: Hierarchical security rebuild

Dynamics CRM TipperWe have not done a mini truckstop for a while so it’s good to bag one at the beginning of the year.


It’s a first time for Greg “Stealth MVP” Olsen to ask the MVP crowd a question.

We have Hierarchy Security setup and working well for an enterprise customer, but not sure how long it takes to apply once we have saved the settings. During our testing it doesn’t look like it’s in real time or immediate.

Can someone inform me on what it does in the background technically and how long I should expect to apply?

There is a good article available, but it doesn’t inform the reader if its instant or takes X minutes/hours etc or what it is doing behind the scenes to set up the security technically.


Adam “First!” Vero does not have his truckstop nickname for nothing, leaving others no chance to answer.

A table called SystemUserManagerMap is built to store who reports to whom and at what level. Eg it will contain a row for Charlie (as the user) and Bob (his boss as “manager”) at level 1, and a second row for Charlie (the user) and Alice (Bob’s boss) at level 2, and a row for Bob > Alice at level 1.

By pre-building this table it should make actual queries much faster (than some horrible iterative query) especially for retrieve multiple – join from current user through this SystemUserManagerMap table (filtered by depth set in configuration) to the entity table to find all records owned by users somewhere in my reporting chain. (In reality it goes SystemUserManagerMap then SystemUserPrincipal table to find all records owned by users who report to you, plus owned by any teams they are in. Likewise through those to the POA table to find records visible to your reporting chain via shares).

If for some reason that table is not built, fails, takes too long etc then queries won’t work properly. From memory, the table is built to represent all depths of reporting (via manager or position depending on settings). Then if you change depth from say 3 to 5, the records already exist and the table is not rebuilt. The table is modified if you change a user’s manager/position (depending on which is in use). Changing from manager to position approach would cause a complete rebuild, as far as I can tell.

Microsoft Scalable Security whitepaper covers more detail: https://www.microsoft.com/en-us/download/details.aspx?id=45905

Tîpp Jäår

The download, even though slightly out of date, contains other useful whitepapers – make sure to study them after closing this window.

Leave a Reply

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