SQL script to retrieve the list of active users with license details D365 FnO
Views (23)
201
Like
Share
ReportHi 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
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
*This post is locked for comments