SQL script to retrieve the list of active users with license details D365 FnO

Hi guys,

I am writing this blog to share the information regarding active users license details in D365 FnO. 

Below is the SQL script that will fetch list of active users, roles and their license details.

Select userId.ID as "User id",
       userId.NAME as "User name", 
       securityRole.NAME AS "Role name",
       securityRole.description AS "Role description",      
       ISNULL(internalOrganization.NAME,'All') as "Legal entity", 
       (CASE securityRole.USERLICENSETYPE
        WHEN 4 Then 'Operations'
        WHEN 6 Then 'Team Members'
        WHEN 7 Then 'Activity'
        Else 'Unknown' end) as "License type"
       from userInfo userid
       inner JOIN SECURITYUSERROLE UserRole
       on UserRole.USER_ = userid.ID
       inner JOIN AXDB.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
       left join SYSUSERLICENSELIST ssul
       on  ssul.username = userid.ID
       left join SYSUSERLICENSECOUNT sslc
       on  sslc.recid = ssul.SYSUSERLICENSECOUNT
where userid.ENABLE = '1'

order by userid.ID