UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER
(
  @APPUSERID uniqueidentifier,
  @BATCHID uniqueidentifier
)
returns bit
as begin
  -- if there is at least one grant and no denials then return true; otherwise, false

  declare @GRANT bit = 0;

  -- if the user is a sysadmin, he is granted access to the batch

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

  if @GRANT = 0 begin

    -- if the user owns the batch or is assigned to its workflow status, he is granted access to the batch

    select
      @GRANT = 1
    from dbo.BATCH
    left outer join dbo.BATCHWORKFLOWSTATUS on BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID
    where (
      (BATCHWORKFLOWSTATUS.APPUSERID = @APPUSERID and BATCH.STATUSCODE = 1)
      or
      BATCH.APPUSERID = @APPUSERID
    )
    and BATCH.ID = @BATCHID;

    if @@ROWCOUNT = 0 begin 
      -- if the user is granted and not denied access to the batch template, he is granted access to the batch


      select top 1
        @GRANT = SECURITY.GRANTORDENY
      from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as SECURITY
        with (noexpand, index(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE_BATCHTEMPLATEID_APPUSERID))               
      inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = SECURITY.BATCHTEMPLATEID
      where SECURITY.APPUSERID = @APPUSERID
      and BATCH.ID = @BATCHID
      and (
          SECURITY.SITEID is null or 
          dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID, SECURITY.SITEID) = 1
      )
      order by SECURITY.GRANTORDENY asc;

      if @@ROWCOUNT = 0 begin
        -- if the user is granted and not denied access to the batch type, he is granted access to the batch


        select top 1
          @GRANT = SECURITY.GRANTORDENY
        from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as SECURITY
        inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = SECURITY.BATCHTYPECATALOGID
        inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID                
        where SECURITY.APPUSERID = @APPUSERID
            and BATCH.ID = @BATCHID
            and (
                  BATCHTEMPLATE.SITEID is null or 
                  dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID, BATCHTEMPLATE.SITEID) = 1
              )
        order by SECURITY.GRANTORDENY asc;
      end
    end
  end         

  return @GRANT;
end