Thursday, 21 May 2015

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.

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

No comments:

Post a Comment