MS-CRM - Query to list the Roles which are in use

I'm going to keep this blog post short and simple.. 

In case you are working on the MS-CRM and came across a situation wherein you want to list the roles that are currently in use in the particular organization then following query would help you out.

Objects used in the query are..

• SystemUserRoles - Holds the association between users of the CRM organization and the roles from it
• RoleBase - Lists the roles across CRM organization
• SystemUserbase - list of users present in the CRM system. Isdisabled column from the table tells us whether user is in enabled or disabled state

Please run this query against your CRM database. I am assuming here that you have READ privileges on the CRM database.

SELECT 

DISTINCT Ro.name AS RolesInUse 

FROM SystemUserRoles sur

INNER JOIN Rolebase ro on ro.RoleId = Sur.RoleId INNER JOIN SystemUserbase su ON su.SystemUserId = sur.Systemuserid  AND  su.Isdisabled = 0