UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE

            CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE
            (
                @APPUSERID uniqueidentifier,
                @SMARTQUERYCATALOGID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier
            )
            returns bit as
            /*
            Returns true if the given user has permissions to the given smart query
            in a role whose security group is either

            1.) Blank and record security mode=0
            2.) Assigned to the given Constituent.
            3.) Blank and site security mode = 1 and the constit has no site assignments

            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.  
            */
            begin
            --If user granted permission to the feature in a role with access to all records then 

            --the user has permission regardless of the record in question.

            if exists
                (
                    select 
                        1 
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SV
                    where 
                        SV.APPUSERID = @APPUSERID AND 
                        SV.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID AND 
                        SV.GRANTORDENY = 1 AND 
                        SV.SITESECURITYMODE = 0
                )
             return 1;


            --Check if the user has permissions to records with no site and if the constituent has no site assignments

            if exists
                (
                    select 
                        1 
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SV
                    where 
                        SV.APPUSERID = @APPUSERID AND 
                        SV.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID AND 
                        SV.GRANTORDENY = 1 AND 
                        SV.SITESECURITYMODE = 1
                )
                 begin
                    --check if constit has no security attributes

                    if not exists(
                                select 
                                    1 
                                from 
                                    dbo.CONSTITUENTSITE 
                                where 
                                    CONSTITUENTSITE.CONSTITUENTID = @CONSTITUENTID)

                                return 1;
                end



            --Next check if constit has any of the sites that the user has been granted in roles that have been granted this feature

            if exists
                (
                    select 
                        1 
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SV
                    where 
                        SV.APPUSERID = @APPUSERID AND 
                        SV.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID AND 
                        SV.GRANTORDENY = 1 AND 
                        (SV.SITESECURITYMODE = 2 or SV.SITESECURITYMODE = 3)
                )
                 begin
                    --check if constit has no security attributes

                    if exists 
                        (
                            select 
                                1 
                            from 
                                dbo.CONSTITUENTSITE
                                inner join dbo.SITEPERMISSION on CONSTITUENTSITE.SITEID = SITEPERMISSION.SITEID
                            where 
                                CONSTITUENTSITE.CONSTITUENTID = @CONSTITUENTID and
                                SITEPERMISSION.APPUSERID = @APPUSERID                        
                        )                            
                                return 1;
                end

            --If neither granted in role with no ringfence or not granted in a ringfence applied to the constit

            --then return false        

            return  0;

            end