UFN_SECURITY_APPUSER_GRANTED_ANY_BATCH

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_ANY_BATCH()
/*
Use this function to get the application users who have access to batch features.

It will check access in the following order.
1. Check if app user has sysadmin rights
2. check if app user has rights to any batch workflow state
3. check if app user has rights to any batch template
4. check if app user has rights to any batch type
5. check if app user is assigned to any batch workflow status for a committed (why?) batch
6. check if app user is assigned to any committed (why?) batch
*/
returns table
as return (
  with USERSWITHSOMESITES (APPUSERID, SITEID) as (
    select SYSTEMROLEAPPUSER.APPUSERID, SITEPERMISSION.SITEID
    from dbo.SYSTEMROLEAPPUSER
    inner join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
    where SYSTEMROLEAPPUSER.SECURITYMODECODE = 2
  ), USERSWITHALLSITES (APPUSERID) as (
    select SYSTEMROLEAPPUSER.APPUSERID
    from dbo.SYSTEMROLEAPPUSER
    where SYSTEMROLEAPPUSER.SECURITYMODECODE = 0
  )
  select BATCH_APP_USERS.ID
  from
  (
    select AU.ID
    from dbo.APPUSER AU
    where AU.ISSYSADMIN = 1

    union

    select SRA_BWS.APPUSERID
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE SRA_BWS with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE_BATCHWORKFLOWSTATEID_APPUSERID))
    where SRA_BWS.GRANTORDENY = 1

    union

    select SRA_UBT.APPUSERID
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE SRA_UBT
    left outer join USERSWITHALLSITES on USERSWITHALLSITES.APPUSERID = SRA_UBT.APPUSERID
    left outer join USERSWITHSOMESITES on SRA_UBT.APPUSERID = USERSWITHSOMESITES.APPUSERID and SRA_UBT.SITEID = USERSWITHSOMESITES.SITEID
    where SRA_UBT.GRANTORDENY = 1 and (SRA_UBT.SITEID is null or USERSWITHALLSITES.APPUSERID is not null or USERSWITHSOMESITES.SITEID is not null)

    union

    select SRA_BRT.APPUSERID
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE SRA_BRT
    where SRA_BRT.GRANTORDENY = 1

    union

    select BATCHWORKFLOWSTATUS.APPUSERID 
    from dbo.BATCH
    inner join dbo.BATCHWORKFLOWSTATUS on BATCH.ID = BATCHWORKFLOWSTATUS.BATCHID
    where BATCH.STATUSCODE = 1

    union

    select BATCH.APPUSERID 
    from dbo.BATCH
    inner join dbo.BATCHWORKFLOWSTATUS on BATCH.ID = BATCHWORKFLOWSTATUS.BATCHID
    where BATCH.STATUSCODE = 1
  ) BATCH_APP_USERS
  inner join dbo.APPUSER AU on AU.ID = BATCH_APP_USERS.ID
);