USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_SYSTEMPRIVILEGES

create procedure dbo.USP_SECURITY_SYSTEMROLEFEATUREFOLDER_GET_SYSTEMPRIVILEGES
(
    @SYSTEMROLEID uniqueidentifier,
    @ONLYASSIGNED bit = 0
)
/* Used by the SecurityGetRoleFeaturePermTreeRequest web service */
as
set nocount on;

IF @SYSTEMROLEID  IS NULL 
    begin
    RAISERROR('@SYSTEMROLEID is required',16,10);
    return 9;
    end;

select
    [CATALOG].ID,
    [CATALOG].NAME,
    [CATALOG].SECURITYUIFOLDER,
    [PERM].GRANTORDENY,
    [CATALOG].DESCRIPTION,
    [PERM].ID as [PERMISSIONID]
from dbo.SYSTEMPRIVILEGECATALOG as [CATALOG]
left outer join
    (
        select ID, GRANTORDENY, SYSTEMPRIVILEGECATALOGID 
        from dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
        where SYSTEMROLEID = @SYSTEMROLEID
    ) as [PERM] on [PERM].SYSTEMPRIVILEGECATALOGID = [CATALOG].ID
where ([PERM].ID is not null) or (@ONLYASSIGNED = 0)
order by [CATALOG].SECURITYUIFOLDER, [CATALOG].NAME