UFN_SYSTEMPRIVILEGE_GETSYSTEMROLEPERMISSIONSLIST

create function dbo.UFN_SYSTEMPRIVILEGE_GETSYSTEMROLEPERMISSIONSLIST(@SYSTEMPRIVILEGEID uniqueidentifier)
returns table
as return (

    select 
        SR.NAME, 
        SRP.SYSTEMROLEID, 
        SRP.GRANTORDENY
    from dbo.SYSTEMROLE as SR 
    inner join dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE as SRP on SR.ID = SRP.SYSTEMROLEID
        and SRP.SYSTEMPRIVILEGECATALOGID = @SYSTEMPRIVILEGEID

    union

    select 
        SR.NAME, 
        SRP.SYSTEMROLEID, 
        SRP.GRANTORDENY --inherit the feature group's grantordeny

    from dbo.SYSTEMROLE as SR 
    inner join dbo.SYSTEMROLEPERM_FEATUREGROUP as SRP on SR.ID = SRP.SYSTEMROLEID
    inner join dbo.FEATUREGROUPFEATURE as FGF on SRP.FEATUREGROUPCATALOGID = FGF.FEATUREGROUPCATALOGID
        and FGF.FEATUREID = @SYSTEMPRIVILEGEID

)