UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY_BYSITE

            CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY_BYSITE
            (
                @APPUSERID uniqueidentifier,
                @SMARTQUERYCATALOGID uniqueidentifier
            )
            returns TABLE as

            /*
            Returns a row for every constituent that the user has rights to according to site 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_NONSITEROLE
            occurs outside this function. If that function returns true there is no need to join to this TVF.

            */

            RETURN

            (
                with SECURITY_CTE as (
                    select distinct
                        SV.SITESECURITYMODE, SITEPERMISSION.SYSTEMROLEID
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SV
                        left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SV.APPUSERID and SITEPERMISSION.SYSTEMROLEID = SV.SYSTEMROLEID
                    where 
                        SV.APPUSERID = @APPUSERID AND 
                        SV.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID AND 
                        SV.GRANTORDENY = 1
                )
                select
                --Check if the user has permissions to records with no site and for constituents that have no site assignments

                    CONSTITUENT.ID
                from
                    dbo.CONSTITUENT
                    cross join (select distinct 1 as SECURE from SECURITY_CTE where SITESECURITYMODE = 1) as ISSECURE
                where
                    not exists (select ID from dbo.CONSTITUENTSITE where CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID)

                union

                select
                --Next check for constituents that have any of the sites that the user has been granted in roles that have been granted this feature

                    CONSTITUENTID
                from 
                    dbo.CONSTITUENTSITE
                    cross join (select distinct 1 as SECURE from SECURITY_CTE where SITESECURITYMODE <> 0 and SITESECURITYMODE <> 1) ISSECURE
                    inner join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = @APPUSERID and CONSTITUENTSITE.SITEID = SITEPERMISSION.SITEID
                    inner join SECURITY_CTE on SECURITY_CTE.SYSTEMROLEID = SITEPERMISSION.SYSTEMROLEID
            )