Querying Users by Security Privilege in Dynamics 365 and Power Platform

Introduction

If you’ve ever needed to identify users within Dynamics 365 or Power Platform with a specific privilege, such as Export to Excel or update Product records, it can be quite arduous to find this info as it could involve reviewing each security role and then running separate queries to find users with that role. The SQL 4 CDS code snippet below will do it in one query.

Prerequisites

Download XrmToolBox and install SQL 4 CDS (kudos to Mark Carrington for this👍).

Run the query

Once you’ve installed the above, open SQL 4 CDS and paste the following query in. This query will show all users with Export to Excel permission.

SELECT u.fullname
FROM systemuser AS u
JOIN systemuserroles AS ur ON ur.systemuserid = u.systemuserid
JOIN role AS r ON r.roleid = ur.roleid
JOIN roleprivileges AS rp ON rp.roleid = r.roleid
JOIN privilege AS p ON p.privilegeid = rp.privilegeid
WHERE p.name = 'prvExportToExcel'

This will displays all users with the privilege stated in the last line. Amend this as required.

Output from the query.

Conclusion

The above it a bit easier than other methods.