UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT

            CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT
            (
                @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 record security mode=1 and the constit has no security groups

            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 no ringfence 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.RECORDSECURITYMODE = 0
                )
             return 1;


            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.RECORDSECURITYMODE = 1
                )
                 begin
                    --check if constit has no security attributes

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

                                return 1;
                end



            --Next check if constit has any of the ringfences 

            --that the user has permissions to the features in the role of

            if exists(
                        select 
                            1 
                        from 
                            dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
                        where 
                            CSAA.CONSTITUENTID = @CONSTITUENTID AND    
                            CSAA.CONSTIT_SECURITY_ATTRIBUTEID in 
                            (
                                select 
                                    SYSTEMROLEAPPUSERCONSTITUENTSECURITY. CONSTITUENTSECURITYATTRIBUTEID
                                from 
                                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SV
                                    inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
                                    inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
                                where
                                    SV.APPUSERID = @APPUSERID and
                                    SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and
                                    SV.RECORDSECURITYMODE = 2 and            
                                    SV.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID and
                                    SV.GRANTORDENY = 1
                            )
                    )
            return 1;

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

            --then return false        

            return  0;

            end