Dynamics Community Forum Thread Details

How to query the assigned roles of each user through ZAP ETL?

Hello, I have an ETL setup with ZAP DataHub that extracts all possible information from our Dynamics F&O application. I'm trying to query all of my users and their currently assigned roles, but I can't seem to find the entity or field that links the user information with the roles.

In dynamics, if I consult any user's information, something like this shows up:

Clearly Dynamics does some internal joins to show the assigned roles of the user, but I can't seem to extract the information. On my ETL (which mind you, was recommended by Microsoft's favorite partner in LATAM but theres sadly not much learning information for it) I have found the table dbo.USERINFO, which basically gives me all the User Details as shown in the image above, but I don't see any field related to roles.
I also have a few tables called SECURITYROLE, and SECURITYSUBROLE, which does give me a list of those roles, but again, no way to link them to each user

What am I missing? I can't seem to find any information regarding this specific case. The automatically generated user-role reports from Dynamics itself are not useful for my needs.
I just need to know what to look for, what field or entity should exist in Dynamics to help me link this information

Categories:

Dynamics 365 Finance

Quick Links