UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATE

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

    --otherwise, false

    declare @grant bit;
    set @grant=0;

    select @grant = ISSYSADMIN from dbo.APPUSER where ID = @APPUSERID;

    if @grant = 0 
        begin
            --order by GRANTORDENY, deny will be first.

            select 
                top 1 @grant=SECURITYVIEW.GRANTORDENY
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE SECURITYVIEW
                with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))
            inner join 
                dbo.BATCHTEMPLATE on SECURITYVIEW.BATCHTEMPLATEID = BATCHTEMPLATE.ID
            where 
                SECURITYVIEW.APPUSERID = @APPUSERID
            and 
                SECURITYVIEW.BATCHTEMPLATEID=@BATCHTEMPLATEID                
            and 
                ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
            order by 
                SECURITYVIEW.GRANTORDENY ASC;
        end

    if @grant = 0 AND @@ROWCOUNT = 0
        begin
            --order by GRANTORDENY, deny will be first.

            select 
                top 1 @grant=SECURITYVIEW.GRANTORDENY
            from 
                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE SECURITYVIEW
            inner join 
                dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = SECURITYVIEW.BATCHTYPECATALOGID
            where 
                SECURITYVIEW.APPUSERID = @APPUSERID
            and 
                BATCHTEMPLATE.ID=@BATCHTEMPLATEID    
            and 
                ((BATCHTEMPLATE.SITEID is null) or (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID,BATCHTEMPLATE.SITEID) = 1))
            order by 
                SECURITYVIEW.GRANTORDENY ASC;
        end                

    return @grant;                
end