Thursday, 16 April 2015

MS-CRM - Query to list the roles that user belongs to in a business unit

One day my client asked me to give him the list of CRM users and their roles as defined in the CRM organization. He also laid down couple of conditions 

  • If a user belongs to more than one role then roles must be displayed as a comma separated string in a single row.
  • We must know whether that user is in disabled state or not
  • What is the business unit of the User

Luckily for me all this information was store in 3 objetcs (Table / view)

  • SystemUser
  • SystemUserRole
  • Role
So given below is what I came up with

 ;WITH UserDetails AS
  , AS RoleAssigned
  ,FRole.businessunitidname AS BU
  ,CASE FUser.isdisabled
END AS isdisabled
FROM FilteredSystemUser FUser
LEFT OUTER JOIN FilteredSystemUserRoles FUserRole
ON FUser.systemuserid FUserRole.systemuserid
LEFT OUTER JOIN FilteredRole FRole
ON FRole.roleid FUserRole.roleid
SELECT ',' RoleAssigned
     FROM UserDetails UDInner
     WHERE UDInner.domainname UDOuter.domainname
UDInner.BU UDOuter.BU
     FOR XML PATH ('')
AS RolesAssigned
FROM UserDetails UDOuter
ORDER BY domainname

No comments:

Post a Comment