Microsoft Dynamics CRM On-Premises | Find Security Role That Has a Certain Privilege

Introduction

Sometimes you will need to show all security roles that are using some privilege. I will show you how to do that using SQL for Microsoft Dynamics CRM On-Premises

Example

Select all security roles that have delete privilege

Code:

SELECT privilege . NAME [Privilege Name] ,
       role . NAME      [Security Role]
FROM   roleprivileges
       JOIN  privilege
         ON privilege . privilegeid =  roleprivileges . privilegeid
       LEFT JOIN  role
              ON role . roleid =  roleprivileges . roleid
WHERE  privilege . NAME LIKE '%delete%'   

That's useful if you're reviewing a big project and need to know for example who has the delete privilege for the account entity, then you can add this condition privilege.NAME LIKE '%account%' so the final SQL query will be as following

Code :

SELECT privilege . NAME [Privilege Name] ,
       role . NAME       [Security Role]
FROM   roleprivileges
       JOIN privilege
         ON  privilege . privilegeid = roleprivileges . privilegeid
       LEFT JOIN role
              ON role . roleid =  roleprivileges . roleid
WHERE  privilege . NAME LIKE  '%delete%'
       AND privilege . NAME LIKE  '%account%'