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
