AX 2012 R3 Licencing for each Role, and the journey to creating it

For months now I keep thinking about creating a way to keep the AX 2012 security spreadsheets up to date, lost countless hours of sleep over it (well I lost a couple of hours over the last couple of days trying to solve this )

Why I hear you ask, well firstly because the licensing spreadsheets on the internet are copies from AX2012 RTM (pre-R2) and are out of date. Secondly because as soon as you try to create your own security the lists gets even more out of date. Many a client have asked me about it and it is handy to be able to see how each role is put together and what impacts the license.

So after days of hard SQL slog, 100 lines of extreme TSQL, some X++ code and avoiding the Eurovision contest (Hooray for bearded folk) you can now access the up to date, R3 compliant security/licensing spreadsheet.
 

Here is a link to an R3 up to date License/Security spreadsheet. Please take it as a rough report, showing you the Roles by number of license entry points in both a high level view and by duties/privileges http://1drv.ms/1mQ35MR. Each entry point is analysed against its license. “None” indicates that only a server license is required.

Also a full analysis spreadsheet can be downloaded with full entry point disclosure e.g. Menu items http://1drv.ms/1mQ2ZF4

Shared as an Excel Online spreadsheet in Onedrive.

The methods I used to achieve this results is worth noting, just in case you want to do this yourselves. To achieve this report I needed four things

• The Entry points for each privilege and the appropriate License
• The complete hierarchy of  all AX security from Roles, Sub-Roles, Duties and Privileges
• The labels for all the AX objects
• A way to combine all the above in a meaningful manner

So the first thing to note is that AX 2012 has a class which calculates the licenses per user, but only needs to do this by entry point and role. The class even has a helpful way to set up the batch job. I am of course referring to

SysUserLicenseMiner::createBatchJob();

 If you look through this class you will find two temporary tables which actually get truncated when the batch job runs. The table I need is the one concerned with licenses

SysUserLicenseMetadataTmp

I simply changed this from a TempDB table to a Regular table, as it is very useful to retain and its is not that big. 

So Entry points at hand I looked at the System Tables and found the Security Tables I needed to work out the hierarchy, better still there are a number of views based on these tables which helped me understand their purpose. Using SQL Management Studio i found out the following tables in the AX_Model database (where AX is replaced by your AX Database name)

• ModelSecurityRole table holds all the key fields for all security artifacts as well as label reference Ids. Please note though this may have duplicate entries to accommodate layers. Hence many views group the records by RoleHandle (the security artifact unique ID) and use Max(RoleLayer) to find the highest layer for the artifact
• ModelSecuritySubRole table contains all the relationships between artifacts e.g. how Duties related to roles
• ModelSecurityPermission is the link between privileges and entry points, but you will need ModelSecurityCommon to go from a Named Entry Point to an ObjectHandle. Please note, entry points may require both an object and a child name e.g. server class and method name.
• ElementTypes is a handy table to lookup types of objects and join this for more meaningful description. You will do well to look up other values in the System enums
• ModelElementLabel is the language specific labels you will need to make things more meaningful to users

So now I have all the data, I had to work out the best way to decipher the info and construct a report. Given my experience in SQL I knew this would be a lot faster and more portable if I used TSQL to do this. TSQL is slower to produce than writing X++ code but it avoids any major system changes and is 100s of times faster from experience, well at least good SQL is faster :)

• I used CTE (Common table expressions) to work through the recursive parts, but I ultimately used it to break the whole problem into unique Roles, unique & cascading sub-roles, unique & cascading duties, unique & cascading privileges and entry points
• I decided that I would adjust the cascading structure and make it more consistent to enhance  readability of complex scenarios and simplify the TSQL, so it should have exactly five levels as follows

• Role 1
• Role 1 acting as a sub-role
• Duties
• Privileges
• Entry Points
• Add Duty to group Role based Privileges 
• Privileges from Role
• Entry Points
• Sub-Role1
• Duties etc
• Flattened Sub-Role 1.1 (from Sub-Role 1)
• Duties Etc
• Roles 2 etc

•  My final joins became easy due to the re-structure
• Select Flattened Roles, Sub-Roles, Duties, Privileges and Entry Point

• Finally I used a Pivot to change the license types into columns to simplify the report for users

And  the report was completed in such a way as to be ultra portable and flattened into an Excel tables. I was so glad I did that when I spotted the server only had Excel 2010 without Power Pivot, especially as I can hear you all complain about the flattened structure and duplication of data I had to use.

I am sure my next version will be a cascading table, separate label table and an entry point fact table for the licenses, but for now this will more than suffice.

I can run the routine any time I like, especially after any security changes and it only takes 30 seconds to populate the report. The entry point batch process runs once a night but I can also run this manually, and this takes 10 minutes to run.

I am not enclosing the TSQL as this is way to long for me to add a full explanation, and I want to change it to accommodate parameters and selection of db names, so for now the extracts will have to suffice, unless you try to contact me and allow me some time to explain it to you personally.

Enjoy the licensing spreadsheet, I know I had fun creating it.