UFN_PROXYUSER_CHECKSITESECURITYRIGHTS

            CREATE function dbo.UFN_PROXYUSER_CHECKSITESECURITYRIGHTS
                (@APPUSERID uniqueidentifier,
                @SITES xml,
                @SYSTEMROLEID uniqueidentifier,
                @SECURITYMODECODE tinyint, 
                @BRANCHSITEID uniqueidentifier, 
                @PROXYOWNERID uniqueidentifier)
            returns bit
            with execute as caller
            as begin

                    declare @HASREQUIREDSITESPERMISSION bit = 0;
                    declare @SITEIDS nvarchar(max) = null;
                    declare @PROXYOWNERSECURITYMODE varchar(50) = null;
                    declare @SITECOUNT int = 0; 

                    select @PROXYOWNERSECURITYMODE = SECURITYMODECODE from dbo.SYSTEMROLEAPPUSER where APPUSERID=@PROXYOWNERID and 
                    SYSTEMROLEID=@SYSTEMROLEID

                    --SECURITYMODECODE = 0 = All Records

                    --SECURITYMODECODE = 1 = Records with no site assigned

                    --SECURITYMODECODE = 2 = Records with one of these sites assigned

                    --SECURITYMODECODE = 3 = Records within a branch


                    --If owner is having permission on All records then no need to check for other permission.

                    if @PROXYOWNERSECURITYMODE = 0
                        begin
                            set @HASREQUIREDSITESPERMISSION = 1;
                        end
                    else
                        begin    
                            --Condition for records with no site assigned


                             if(@SECURITYMODECODE = 1 and @PROXYOWNERSECURITYMODE = 1)
                                begin
                                    set @HASREQUIREDSITESPERMISSION = 1;
                                end
                            --Condition for records with records with one of this site assigned

                            else if ( @SECURITYMODECODE = 2 and @PROXYOWNERSECURITYMODE = 2)
                                begin

                                    declare @SITESTABLE table (siteid uniqueidentifier);
                                    insert into @SITESTABLE
                                    select t.x.value('SITEID[1]','uniqueidentifier') 
                                    from @SITES.nodes('/SITES/ITEM') t(x);

                                    select @SITECOUNT = count(1) from @SITESTABLE;

                                    If ((select count(1) from SYSTEMROLEAPPUSERSITE 
                                        inner join SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.ID = SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID
                                        inner join @SITESTABLE as SITETABLE on SYSTEMROLEAPPUSERSITE.SITEID = SITETABLE.SITEID
                                        where SYSTEMROLEAPPUSER.APPUSERID=@PROXYOWNERID and SYSTEMROLEAPPUSER.SYSTEMROLEID=@SYSTEMROLEID) >= @SITECOUNT or 
                                        dbo.UFN_PROXYUSER_CHECKSITEHIERARCHYPERMISSION(@PROXYOWNERID,@SITES,@SITECOUNT,@SYSTEMROLEID) = 1)
                                            begin
                                                set @HASREQUIREDSITESPERMISSION = 1;
                                            end
                                end
                                --Condition for records within a branch         

                            else if (@SECURITYMODECODE = 3 and @PROXYOWNERSECURITYMODE = 3)
                                begin
                                    if((select count(1) from dbo.SYSTEMROLEAPPUSER where APPUSERID=@PROXYOWNERID and SYSTEMROLEID=@SYSTEMROLEID and
                                        BRANCHSITEID = @BRANCHSITEID)>0 or 
                                       (dbo.UFN_PROXYUSER_CHECKBRANCHHIERARCHYPERMISSION(@PROXYOWNERID,@SYSTEMROLEID,@BRANCHSITEID) = 1))
              set @HASREQUIREDSITESPERMISSION = 1;
                                    else
                                        set @HASREQUIREDSITESPERMISSION = 0;
                                end
                        end

                return @HASREQUIREDSITESPERMISSION
            end