UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE
(@APPUSERID uniqueidentifier,@SYSTEMPRIVILEGEID uniqueidentifier)
returns bit
as
/*
Returns true if user has been granted and not denied the report for a System Role.
*/
begin
    --If at least one grant and no deny then return true

    --otherwise, false

    declare @grant bit;
    set @grant=0;

    --order by GRANTORDENY, deny will be first.


    select top 1 @grant=GRANTORDENY

    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE

    where APPUSERID = @APPUSERID
    and SYSTEMPRIVILEGECATALOGID=@SYSTEMPRIVILEGEID

    order by GRANTORDENY ASC;


    return @grant;

end