Monday 31 July 2023

XRMTOOLBOX SQL 4 CDS to get logged in users, Users with security roles in Dynamics 365

Hello Everyone,



Today i am going to show the XRMTOOLBOX tool SQL 4 CDS which allows you to use standard SQL  syntax to query and manipulate your data and metadata in Dataverse and Dynamics 365.






Let's gets started.



Here is the link for sql 4 cds 





SQL 4 CDS tool Capabilities:



First of all many thanks to creator of this tool Mark Carrington.




As per the release notes:


Add support for XML data type and FOR XML clause.

Add support for Elastic tables

Add support for Stuff function

Add option to bypass plugins for SELECT statements

Improved error reporting for duplicated table/alias names

Improved error reporting for plugin errors

Improved efficiency of joins that can't be translated to FetchXML

Improved error handling during bulk DML Operations

Fixed querying audit table

Fixed Collation label for metadata and virtual columns

Fixed hash joins on different collations

Fixed filtering on outer-joined solution table

Fixed multi threading error with partitioned aggregations

Fixed copying results with headers but no data rows. 




I have used this tool for find out the users with role, below is the script:

SELECT identityid,
       systemuser.internalemailaddress,
       systemuser.fullname,
       systemuser.title,
       systemuser.systemuserid,
       systemuser.isdisabled,
       systemuser.islicensed,
       account.name,
       businessunit.name,
       role.roleid,
       businessunit.name,
       role.name
FROM   systemuserroles
       INNER JOIN
       systemuser AS systemuser
       ON systemuser.systemuserid = systemuserroles.systemuserid
          AND (systemuser.isdisabled = 0
               AND systemuser.accessmode = 0)
       LEFT OUTER JOIN
       account AS account
       ON account.accountid = systemuser.gmr_opcoid
       INNER JOIN
       businessunit AS businessunit
       ON businessunit.businessunitid = systemuser.businessunitid
       INNER JOIN
       role AS role
       ON role.roleid = systemuserroles.roleid
          AND (role.name = ' Admin All'
            OR role.name = ' Admin Owned'
            OR role.name = ' Admin Project'
            OR role.name = ' Analytics'
            OR role.name = ' Basic '
            OR role.name = ' Basic Client'
            OR role.name = ' BI Integration'
            OR role.name = '  Embedded Power BI Report'
            OR role.name = ' Embedded Reports OC'
            OR role.name = ' Financial Controller'
            OR role.name = ' Import/Export'
            OR role.name = 'Integration'
            OR role.name = ' Admin'
            OR role.name = ' Manager'
            OR role.name = ' Integration'
            OR role.name = ' Service Account');



Also for last logged in users:



SELECT   a.actionname,
         a.objectid AS systemuserid,
         s.fullname,
         s.domainname,
       (SELECT   TOP 1 c.createdon
          FROM     audit AS c
                   INNER JOIN
                   systemuser AS systemuser
                   ON systemuser.systemuserid = c.objectid
                      AND c.action = 64
                      AND c.createdon >= dateadd(month, -6, GETDATE())
          WHERE    c.objectid = a.objectid
          ORDER BY c.createdon DESC) AS lastloginutc
FROM     audit AS a
         JOIN
         systemuser AS s
         ON a.objectid = s.systemuserid
            AND a.action = 64
            AND a.createdon >= dateadd(month, -6, GETDATE())
         RIGHT OUTER JOIN
         systemuserroles AS systemuserroles
         ON systemuserroles.systemuserid = s.systemuserid
         RIGHT OUTER JOIN
         role AS role
         ON role.roleid = systemuserroles.roleid
            AND role.name LIKE 'GMR SEC%'
GROUP BY a.actionname, a.objectid, s.fullname, s.domainname, s.internalemailaddress
ORDER BY lastloginutc DESC;







That's it for today


I hope this helps.
Malla Reddy(@UK365GUY)
#365BlogPostsin365Days

No comments:

Post a Comment

Note: only a member of this blog may post a comment.