UFN_SECURITY_SYSTEMPRIVILEGE_GETASSIGNMENTLIST

Gets a list of system roles along with a flag indicating their permissions to the specified system privilege.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@SYSTEMPRIVILEGECATALOGID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_SECURITY_SYSTEMPRIVILEGE_GETASSIGNMENTLIST(@SYSTEMPRIVILEGECATALOGID uniqueidentifier)
returns table
as
    return (
        select SYSTEMROLE.ID SYSTEMROLEID,
            SYSTEMROLE.NAME SYSTEMROLENAME,
            (select GRANTORDENY from dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
                left join dbo.SYSTEMPRIVILEGECATALOG on SYSTEMROLEPERM_SYSTEMPRIVILEGE.SYSTEMPRIVILEGECATALOGID = SYSTEMPRIVILEGECATALOG.ID
                where SYSTEMROLEPERM_SYSTEMPRIVILEGE.SYSTEMROLEID = SYSTEMROLE.ID 
                    and SYSTEMPRIVILEGECATALOG.ID = @SYSTEMPRIVILEGECATALOGID) GRANTORDENY
        from dbo.SYSTEMROLE
    );