UFN_SECURITY_WEBDASHBOARDPAGE_GETASSIGNMENTSLIST

CREATE function dbo.UFN_SECURITY_WEBDASHBOARDPAGE_GETASSIGNMENTSLIST(@WEBDASHBOARDPAGEID uniqueidentifier)
/*
Returns a table of IDs for all system roles that have been assigned the given web dashboard page
*/
returns table
as
return 
(

select  
    SRW.ID,
    SR.NAME, 
    SR.ID SYSTEMROLEID, 
    cast(case when exists(select ID 
        from dbo.SYSTEMROLEWEBDASHBOARDPAGE 
        where SYSTEMROLEWEBDASHBOARDPAGE.WEBDASHBOARDPAGEID = @WEBDASHBOARDPAGEID
            and SYSTEMROLEWEBDASHBOARDPAGE.SYSTEMROLEID = SR.ID
            and SYSTEMROLEWEBDASHBOARDPAGE.GRANTORDENYVIEW = 1) 
    then 1 
    else 0 
    end as bit) as GRANTORDENYVIEW,
    cast(case when exists(select ID 
        from dbo.SYSTEMROLEWEBDASHBOARDPAGE 
        where SYSTEMROLEWEBDASHBOARDPAGE.WEBDASHBOARDPAGEID = @WEBDASHBOARDPAGEID
            and SYSTEMROLEWEBDASHBOARDPAGE.SYSTEMROLEID = SR.ID
            and SYSTEMROLEWEBDASHBOARDPAGE.GRANTORDENYEDIT = 1) 
    then 1 
    else 0 
    end as bit) as GRANTORDENYEDIT
from dbo.SYSTEMROLE as SR 
    left outer join dbo.SYSTEMROLEWEBDASHBOARDPAGE as SRW on SR.ID = SRW.SYSTEMROLEID
    where SRW.WEBDASHBOARDPAGEID = @WEBDASHBOARDPAGEID
);