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
(SELECT
  
fullname
  ,domainname
  ,FRole.name AS RoleAssigned
  ,FRole.businessunitidname AS BU
  ,CASE FUser.isdisabled
   WHEN THEN 'YES'
   
ELSE 'NO'
  
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
  DISTINCT
  
fullname
  ,domainname
  ,BU
  ,isdisabled
  ,STUFF(
    (
     
SELECT ',' RoleAssigned
     FROM UserDetails UDInner
     WHERE UDInner.domainname UDOuter.domainname
      AND
      
UDInner.BU UDOuter.BU
     FOR XML PATH ('')
    ),
1,1,''
   
AS RolesAssigned
FROM UserDetails UDOuter
ORDER BY domainname
  



No comments:

Post a Comment

bloggerwidgets