UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORQUERYVIEW

CREATE function BBDW.[UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORQUERYVIEW]
(  
@APPUSERID uniqueidentifier,  
@QUERYVIEWID uniqueidentifier  
)  
returns table as  

/*  
Returns a row for every constituent that the the user has rights to according to record access security.  

This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.ConstitRecordSecurityService  
class which implements the RecordSecurity service for Constituent record security.  

As such, it assumes that a check for DENY occurs outside this function  
and also assumes that a check for if the user is ISSYSADMIN occurs outside   
this function.    

It also assumes a check for UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_NONRACROLE   
occurs outside this function. If that function returns true there is no need to join to this TVF.  

*/  
return  
(       
select   
 csa.[CONSTITUENTSYSTEMID] AS ID   
from   
 BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] as csa  
where  
 csa.[CONSTITUENTSECURITYGROUPDIMID] in
  (  
   select   
    cs.[CONSTITUENTSECURITYGROUPDIMID]
   from   
    BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW] as sv  
    inner join BBDW.[FACT_SYSTEMROLEAPPUSER] sap on sap.[SYSTEMROLEAPPUSERFACTID] = sv.[SYSTEMROLEAPPUSERFACTID]
    inner join BBDW.[FACT_SYSTEMROLEAPPUSERCONSTITUENTSECURITY] as cs on cs.[SYSTEMROLEAPPUSERFACTID] = sv.[SYSTEMROLEAPPUSERFACTID]
   where  
    sv.[APPUSERID] = @APPUSERID and 
    sv.[QUERYVIEWCATALOGID] = @QUERYVIEWID and 
    sv.[GRANTORDENY] = 1 and  
    sap.[APPUSERSYSTEMID] = @APPUSERID and  
    sap.[CONSTITUENTSECURITYMODE] = 2   
  )        
union all  
--Constits with no security attributes if the user in a role with security mode = 1  
select   
 [CONSTITUENTSYSTEMID] as [ID]
from   
 BBDW.[DIM_CONSTITUENT]
where   
 exists  
  (select   
   1  
  from   
   BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW] as sv  
  where  
   sv.[APPUSERID] = @APPUSERID and 
   sv.[QUERYVIEWCATALOGID] = @QUERYVIEWID and
   sv.[GRANTORDENY] = 1 and
   sv.[CONSTITUENTSECURITYMODE] = 1
  )      
 and 
 [CONSTITUENTDIMID] not in  
  (  
  select   
    [CONSTITUENTDIMID]
  from   
    BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] 
  )  
);