USP_SYSTEMROLE_APPUSERLIST

            CREATE procedure dbo.USP_SYSTEMROLE_APPUSERLIST
            (
                @SYSTEMROLEID uniqueidentifier
            )
            as
                --Used by the System Role page

                set nocount on;

                with CTE_ASSIGNED_SYSTEM_ROLES(APPUSERID, ASSIGNED_SYSTEM_ROLES) as
                (
                    select
                        SYSTEMROLEAPPUSER.APPUSERID,
                        COUNT(1) as ASSIGNED_SYSTEM_ROLES
                    from 
                        dbo.SYSTEMROLEAPPUSER 
                    group by
                        SYSTEMROLEAPPUSER.APPUSERID
                ), CTE_SYSTEMROLEAPPUSERSITE(SYSTEMROLEAPPUSERID, SITELIST) as
                (
                    select
                        SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID,
                        dbo.UDA_BUILDLIST(SITE.NAME) as SITELIST 
                    from 
                        dbo.SITE 
                        inner join dbo.SYSTEMROLEAPPUSERSITE ON SYSTEMROLEAPPUSERSITE.SITEID = SITE.ID
                    group by
                        SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID
                ), CTE_SYSTEMROLEAPPUSERCONSTITUENTSECURITY(SYSTEMROLEAPPUSERID, CONSTITUENTSECURITYGROUPLIST) as
                (
                    select
                        SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID,
                        dbo.UDA_BUILDLIST(CONSTIT_SECURITY_ATTRIBUTE.NAME) as CONSTITUENTSECURITYGROUPLIST
                    from
                        dbo.CONSTIT_SECURITY_ATTRIBUTE 
                        inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY ON SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID = CONSTIT_SECURITY_ATTRIBUTE.ID
                    group by
                        SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID
                )
                select 
                    SYSTEMROLEAPPUSER.ID,
                    APPUSER.ID AS APPUSERID,
                    APPUSER.USERSID,
                    case
                    when isnull(APPUSER.ISPROXYUSER,0) = 0 then COALESCE(SUSER_SNAME(APPUSER.USERSID),'')
                    else APPUSER.CUSTOM_AUTHENTICATION_USERID
                    end as USERNAME,
                    APPUSER.DISPLAYNAME,
                    CTE_ASSIGNED_SYSTEM_ROLES.ASSIGNED_SYSTEM_ROLES,
                    SYSTEMROLEAPPUSER.FROMROLESYNC,
                    SYSTEMROLEAPPUSER.SECURITYMODECODE,
                    SYSTEMROLEAPPUSER.SECURITYMODE,
                    CTE_SYSTEMROLEAPPUSERSITE.SITELIST,
                    SITE.NAME as BRANCHSITENAME,
                    SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE,
                    SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODE,
                    CTE_SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYGROUPLIST,
                    APPUSER.CUSTOM_AUTHENTICATION_USERID,
                    APPUSER.EMAILADDRESS
                from
                    dbo.SYSTEMROLEAPPUSER 
                    inner join dbo.APPUSER on SYSTEMROLEAPPUSER.APPUSERID=APPUSER.ID
                    left join dbo.SITE on SITE.ID = SYSTEMROLEAPPUSER.BRANCHSITEID
                    left join CTE_ASSIGNED_SYSTEM_ROLES on CTE_ASSIGNED_SYSTEM_ROLES.APPUSERID = APPUSER.ID
                    left join CTE_SYSTEMROLEAPPUSERSITE on CTE_SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
                    left join CTE_SYSTEMROLEAPPUSERCONSTITUENTSECURITY on CTE_SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
                where 
                    SYSTEMROLEAPPUSER.SYSTEMROLEID=@SYSTEMROLEID