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

================================================================

Comments

Post a Comment

Popular posts from this blog

Customization on Sales invoice Report in D365 F&O

-
 We have a requirement to create a new design and show some new fields in the sales invoice report.  So for that we need to consider the below steps :-  1) Controller class than extends standard class and create alogic. 2) Create a print management class to call the new design. 3) Extension of DP class to write a logic. 4) Report design. ==================================================================== 1) Controller class : -  /// <summary> /// Extension of the controller class to call the custom design of report "MMSSalesInvoice.report" for MMS legal entity. /// </summary> class MMSSalesInvoiceController extends SalesInvoiceController {        public static MMSSalesInvoiceController construct()         {             return new MMSSalesInvoiceController();         }         public static void main(Args _args)         { ...
Read more

75) COC - Create a coc of the table modified method

-
 /// <summary> /// The <c>AvaEntAssetChecklistVariableOptionsDbt_Extension</c> contains the TransMountain extending logic for the <c>EntAssetChecklistVariableOptions</c> table. /// </summary> [ExtensionOf(tableStr(EntAssetChecklistVariableOptions))] final class AvaEntAssetChecklistVariableOptionsDbt_Extension {     /// <summary>     /// Processes the modified field event for TransMountain fields.     /// </summary>     /// <param name = "_fieldId">reference to field id</param>     public void modifiedField(FieldId _fieldId)     {         EntAssetChecklistVariableOptions entAssetChecklistVariableOptions;         next modifiedField(_fieldId);         // Default must be marked for only one check list line for the varaible.         if (_fieldId == fieldNum(EntAssetChecklistVariableOptions,...
Read more

46) D365 FO: SHAREPOINT FILE UPLOAD USING X++

-
Image
Requirement : Uploading a file in Sharepoint online using X++ and inside from Microsoft Dynamics 365 finance and operations. Prerequisites: The current user id of Microsoft Dynamics 365 Finance and operations should have proper rights and privileged in Sharepoint Online. Sample Code: In this scenario i am creating a text file in Microsoft Dynamics 365 finance and operations and then uploading its memory Stream in Sharepoint online using X++ . You can modify the X++ code in D365 finance and operations as per your need. class RunnableClassRun { /// /// Runs the class with the specified arguments. /// /// The specified arguments. public static void main(Args _args) { System.UriBuilder builder = new System.UriBuilder(“ https://XXXXXX.sharepoint.com&#8221 ;); str extId = xUserInfo::getExternalId(); Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider provider; Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation documentLocation ...
Read more