UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY]
(
    @APPUSERID uniqueidentifier,
    @SMARTQUERYID 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 within a smart query.
which respects the constituent security policy that has been configured.

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_SMARTQUERY_IN_NONRACROLE 
occurs outside this function. If that function returns true there is no need to join to this TVF.

*/

return

(                    
select 
  csaa.[CONSTITUENTSYSTEMID] AS ID   
from 
    BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] as csaa
where
  csaa.[CONSTITUENTSECURITYGROUPDIMID] in
            (
                select 
                    cs.[CONSTITUENTSECURITYGROUPDIMID]
                from 
                    BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY] 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
                    sap.[CONSTITUENTSECURITYMODE] = 2  and
                    sv.[SMARTQUERYCATALOGID] = @SMARTQUERYID and
                    sv.[GRANTORDENY] = 1                                
                )

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_SMARTQUERY] as sv
        where
            sv.[APPUSERID] = @APPUSERID and 
            sv.[SMARTQUERYCATALOGID] = @SMARTQUERYID and 
            sv.[GRANTORDENY] = 1 and 
            sv.[CONSTITUENTSECURITYMODE] = 1                
    )                
 and 
 [CONSTITUENTDIMID] not in  
  (  
  select   
    [CONSTITUENTDIMID]
  from   
    BBDW.[FACT_CONSTITUENTSECURITYGROUPASSIGNMENT] 
  )  
);