UFN_SECURITY_APPUSER_GRANTED_TASK_FORCONSTIT

            CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_TASK_FORCONSTIT
            (
            @APPUSERID uniqueidentifier,
            @TASKID uniqueidentifier,
            @CONSTITUENTID uniqueidentifier
            )
            returns bit

            /*
            Returns true if the given user has permissions to the given task
            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.SYSTEMROLETASK as ST
                        inner join dbo.SYSTEMROLE on ST.SYSTEMROLEID = SYSTEMROLE.ID        
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLE.ID = SYSTEMROLEAPPUSER.SYSTEMROLEID                        
                    where 
                        SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and 
                        ST.TASKID = @TASKID and 
                        SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 0
                )
             return 1;


            if exists
                (
                    select 
                        1 
                    from 
                        dbo.SYSTEMROLETASK as ST
                        inner join dbo.SYSTEMROLE on ST.SYSTEMROLEID = SYSTEMROLE.ID        
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLE.ID = SYSTEMROLEAPPUSER.SYSTEMROLEID                        
                    where 
                        SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and 
                        ST.TASKID = @TASKID and 
                        SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 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
                        where 
                            CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT.CONSTITUENTID = @CONSTITUENTID and    
                            CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT.CONSTIT_SECURITY_ATTRIBUTEID in 
                            (
                                select
                                    SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
                                from 
                                    dbo.SYSTEMROLETASK as ST
                                    inner join dbo.SYSTEMROLE on ST.SYSTEMROLEID = SYSTEMROLE.ID        
                                    inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLE.ID = SYSTEMROLEAPPUSER.SYSTEMROLEID    
                                    inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSER.ID = SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID
                                where
                                    SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and 
                                    ST.TASKID = @TASKID and
                                    SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 2
                            )
                    )
            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