Share
Report
35Hi guys,
I've been trying to put together a list of all our active users, with information on their roles, what license type they require and what legal entities they have access to, but I'm struggling to get my head around the license tables in SQL.
So far I've got this:
Select userId.ID,userId.NAME,securityRole.AOTNAME AS Role,
ISNULL(internalOrganization.NAME,'All') as Legal_Entity
from userInfo userid
inner JOIN SECURITYUSERROLE UserRole
on UserRole.USER_ = userid.ID
inner JOIN AX_Dynamics_UAT_model.dbo.SECURITYROLE securityRole
on securityRole.RecId = UserRole.SecurityRole
LEFT JOIN OMUserRoleOrganization roleOrganization
on roleOrganization.SecurityRole = UserRole.SecurityRole
AND roleOrganization.USER_ = userid.Id
LEFT JOIN DIRPARTYTABLE internalOrganization
on internalOrganization.RECID = roleOrganization.OMINTERNALORGANIZATION
where userid.ENABLE = '1'
order by userid.ID ASC
This gives me everything except the license type. I know you can get the license type from the Named User License Count report, but I can't work out how to put it into my query. Can anyone offer any advice?
Apologies if the query is badly formatted, the forum post removed my formatting
Seal
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
