UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_SYSTEMROLE

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_SYSTEMROLE(@APPUSERID uniqueidentifier,@QUERYVIEWID uniqueidentifier)
  returns bit
as
  /* 
  Returns true if user has been granted and not denied the query view for a System Role (non-record-access 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_QUERYVIEW (nolock)
    where APPUSERID = @APPUSERID
    and QUERYVIEWCATALOGID=@QUERYVIEWID
    order by GRANTORDENY ASC;

    return @GRANT;
end