fnUserSearchablePages

            CREATE   FUNCTION [dbo].[fnUserSearchablePages]
                (@ClientsID int, @ClientUsersID int, @SiteContentFilter int, @PageContentFilter int, @CurrentDate datetime)
            RETURNS @UserSearchableParts TABLE (
                PageContentID int,
                PageID int,
                SiteContentID int,
                PageName nvarchar(255)
            )AS
            BEGIN
                DECLARE @TemplateSecurity table(ObjectGuid uniqueidentifier PRIMARY KEY, [ID] int)
                DECLARE @PageSecurity table(ObjectGuid uniqueidentifier PRIMARY KEY, [ID] int, TemplatePageID int, PageName nvarchar(255) COLLATE database_default)
                DECLARE @SiteContentSecurity table(ObjectGuid uniqueidentifier PRIMARY KEY, [ID] int) --Part Security

                DECLARE @TargetedPartsSecurity table (PartOrder int, PageContentID int, PageID int, SiteContentID int, PageName nvarchar(255))

                IF @SiteContentFilter <= 0
                    SET @SiteContentFilter = null
                IF @PageContentFilter <= 0
                    SET @PageContentFilter = null

                --Determine the Templates the user can view

                INSERT INTO @TemplateSecurity
                SELECT ts.ObjectGuid, t.ID
                FROM dbo.fnUserPrivs(@ClientUsersID,@ClientsID,3) ts
                INNER JOIN sitepages t on t.guid = ts.ObjectGUID and t.IsTemplatePage = 1
                WHERE ts.canView = 1

                --Determine the Pages the user can view

                INSERT INTO @PageSecurity
                SELECT ps.ObjectGuid, p.ID, p.TemplatePageID, p.PageName
                FROM dbo.fnUserPrivs(@ClientUsersID,@ClientsID,2) ps
                INNER JOIN sitepages p on p.guid = ps.ObjectGUID and p.IsTemplatePage = 0
                WHERE ps.canView = 1

                --Insert Parts that are indexed in Search

                INSERT INTO @SiteContentSecurity
                SELECT scs.ObjectGuid, sc.id
                FROM dbo.fnUserPrivs(@ClientUsersID,@ClientsID,1) scs
                INNER JOIN sitecontent sc on sc.guid = scs.ObjectGUID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                WHERE canView = 1
                AND sc.id IN (SELECT DISTINCT SiteContentID From Search)
                AND isnull(@SiteContentFilter,sc.id) = sc.id

                --Insert TargetedContentParts Parts Are Searchable by the user

                INSERT INTO @SiteContentSecurity
                SELECT distinct scs.ObjectGuid, sc.id
                FROM dbo.fnUserPrivs(@ClientUsersID,@ClientsID,1) scs
                INNER JOIN sitecontent sc on sc.guid = scs.ObjectGUID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
                INNER JOIN TargetedContentPart [tcp] on tcp.sitecontentid = sc.id
                WHERE canView = 1
                AND sc.ContentTypesID = 66
                AND EXISTS (SELECT *
                FROM @SiteContentSecurity s
                INNER JOIN TargetedContentChecks tcc ON tcc.ContentID = s.ID
                INNER JOIN TargetedContentPart [tcp] ON tcc.TargetedPartID = tcp.ID
                WHERE isnull(@SiteContentFilter,s.id) = s.id
                )
                AND NOT EXISTS (SELECT *
                FROM @SiteContentSecurity s
                WHERE s.ID = sc.ID
                and isnull(@SiteContentFilter,s.id) = s.id
                )


                --Select all TC parts the user has permissions for. Will filter by priority afterwards.

                INSERT INTO @TargetedPartsSecurity
                --Pages that contain a Targeted Content Part That Contain the Part

                SELECT tcc.PartOrder, pc.id, ps.ID, scs.id, ps.PageName
                FROM @SiteContentSecurity scs
                INNER JOIN TargetedContentChecks tcc on tcc.ContentID = scs.ID
                INNER JOIN TargetedContentPart [tcp] on tcp.ID = tcc.TargetedpartID
                INNER JOIN @SiteContentSecurity tcpscs on tcpscs.[ID] = tcp.SiteContentID
                INNER JOIN pagecontent pc on pc.sitecontentid = tcpscs.ID AND isnull(@PageContentFilter,pc.id) = pc.id
                INNER JOIN @PageSecurity ps  on ps.ID = pc.sitepagesid
                INNER JOIN @TemplateSecurity ts  on ts.ID = ps.TemplatePageID
                UNION
                --Pages whose template contain a Targeted Content Part That Contain the Part

                SELECT tcc.PartOrder, pc.id, ps.ID, scs.id, ps.PageName
                FROM @SiteContentSecurity scs
                INNER JOIN TargetedContentChecks tcc on tcc.ContentID = scs.ID
                INNER JOIN TargetedContentPart [tcp] on tcp.ID = tcc.TargetedpartID
                INNER JOIN @SiteContentSecurity tcpscs on tcpscs.[ID] = tcp.SiteContentID
                INNER JOIN pagecontent pc on pc.sitecontentid = tcpscs.ID AND isnull(@PageContentFilter,pc.id) = pc.id
                INNER JOIN @TemplateSecurity ts on ts.[id] = pc.sitepagesid
                INNER JOIN @PageSecurity ps on ps.TemplatePageID = ts.[id]



                INSERT INTO @UserSearchableParts
                --Pages that contain the part

                SELECT pc.id, ps.ID, scs.id, ps.PageName
                FROM @SiteContentSecurity scs
                INNER JOIN pagecontent pc on pc.sitecontentid = scs.ID AND isnull(@PageContentFilter,pc.id) = pc.id
                INNER JOIN @PageSecurity ps  on ps.ID = pc.sitepagesid
                INNER JOIN @TemplateSecurity ts  on ts.ID = ps.TemplatePageID
                UNION
                --Pages whose template contain the part

                SELECT pc.id, ps.ID, scs.id, ps.PageName
                FROM @SiteContentSecurity scs
                INNER JOIN pagecontent pc on pc.sitecontentid = scs.ID AND isnull(@PageContentFilter,pc.id) = pc.id
                INNER JOIN @TemplateSecurity ts on ts.[id] = pc.sitepagesid
                INNER JOIN @PageSecurity ps on ps.TemplatePageID = ts.[id]
                UNION
                --A filtered version of the templates and pages containing targeted content

                SELECT tps.PageContentID, tps.PageID, tps.SiteContentID, tps.PageName
                FROM @TargetedPartsSecurity tps
                INNER JOIN (SELECT min(PartOrder) [PartOrder], PageContentID, PageID FROM @TargetedPartsSecurity GROUP BY PageContentID, PageID) tps2
                on tps.PageContentID=tps2.PageContentID and tps.PartOrder=tps2.PartOrder

            RETURN
            END