MS-CRM - Query to List Roles assigned to Active users

This requirement came when I was working on the MS-CRM migration project and my client wanted to know ROLE's that ACTIVE users are part of....

In MS-CRM following objects hold this information

• SystemUserBase - Contains User related information. It also has a Isdisabled column which gives the information about which one's are active and which one's are not

• RoleBase - List of roles that are defined in the MS-CRM Organization

• SystemUserRole - defines the relationship between user and the role.

Following query will give fetch us the roles and the count of active users belonging to that role.

SELECT FRole.name AS RoleName
,COUNT(1) NoOfUsersPartOfRole
FROM SystemUserbase FUser
INNER JOIN SystemUserRoles FUserRole
ON FUser.systemuserid = FUserRole.systemuserid
INNER  JOIN Rolebase FRole
ON FRole.roleid = FUserRole.roleid
WHERE FUser.isdisabled = 0
GROUP BY FRole.name