USP_DATALIST_APPLICATIONUSER_MANUALLYADDEDCMSROLES

create procedure dbo.USP_DATALIST_APPLICATIONUSER_MANUALLYADDEDCMSROLES
(
    @APPUSERID uniqueidentifier
)
as
    set nocount on;

    declare @ClientUsersId int
    select 
        @ClientUsersId = ClientUsers.ID 
    from dbo.ClientUsers
        join dbo.BBNCUSERMAP on dbo.ClientUsers.UserName=BBNCUSERMAP.BBNCUSERNAME
    where BBNCUSERMAP.ID=@APPUSERID

    SELECT    
        ClientRoles.[ID],
        ClientRoles.[ClientsID] [CLIENTSID],
        ClientRoles.[Name] [NAME]

    from dbo.ClientRoles
        left outer join dbo.fnUserRoles(@ClientUsersID, 1) fUR on fUR.ClientRolesID = ClientRoles.ID
        left outer join dbo.UserRoles UR on UR.ClientUsersID = @ClientUsersID and UR.ClientRolesID = ClientRoles.ID

    WHERE ClientRoles.Deleted=0 AND ClientRoles.EveryoneRole=0
        and CAST(ISNULL(fUR.ClientRolesID, 0) as bit)=1 --member

        and CAST(ISNULL(UR.ManuallyAdded, 0) as bit) = 1 --manually added


    ORDER BY ClientRoles.[Name]