SELECT
UserMaster.USERID AS UserID,
UserMaster.USERNAME AS Username,
ISNULL(CompanyMaster.INTERID, '') AS CompanyID,
ISNULL(CompanyMaster.CMPNYNAM, '') AS CompanyName,
(STUFF((SELECT CAST(', ' + RTRIM(UserRole.SECURITYROLEID) AS VARCHAR(MAX))
FROM dynamics..SY10500 AS UserRole
WHERE UserRole.cmpanyid = UserCompanyAccess.CMPANYID AND UserRole.USERID = UserCompanyAccess.USERID
FOR XML PATH ('')), 1, 2, '')
) AS Holds ,
CASE WHEN pu.USERID IS NULL THEN '' ELSE 'Yes' END AS PowerUser
FROM dynamics..SY01400 AS UserMaster
LEFT JOIN dynamics..SY40400 AS ClassMaster ON ClassMaster.USRCLASS = UserMaster.USRCLASS
LEFT JOIN dynamics..SY60100 AS UserCompanyAccess ON UserCompanyAccess.USERID = UserMaster.USERID
LEFT JOIN dynamics..SY01500 AS CompanyMaster ON CompanyMaster.CMPANYID = UserCompanyAccess.CMPANYID
LEFT JOIN dynamics..SY10500 AS pu ON pu.CMPANYID = UserCompanyAccess.CMPANYID AND pu.USERID = UserCompanyAccess.USERID AND pu.SECURITYROLEID = 'poweruser'
ORDER BY 1, 3