fnUserPrivs

            CREATE  FUNCTION [dbo].[fnUserPrivs]
            (
                @ClientUsersID int,
                @ClientsID int,
                @ObjectTypeFilter int
            )

            RETURNS TABLE
            AS
            RETURN
            SELECT 
            O.Guid ObjectGuid,
            1 ObjectTypeID,
            CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canView,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN SiteContent O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,1) AND O.DELETED = 0 AND O.GUID IS NOT NULL
            GROUP BY O.GUID,O.OwnerID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                2 ObjectTypeID,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canView,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN SitePages O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,2,3) AND O.DELETED = 0 AND O.GUID IS NOT NULL
            GROUP BY O.GUID,O.OwnerID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                5 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN ApplicationTasks O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,5) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                12 ObjectTypeID,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canView,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN EmailList O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,12) AND O.DELETED = 0 AND O.GUID IS NOT NULL
            GROUP BY O.GUID,O.OwnerID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                14 ObjectTypeID,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canView,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN EmailTemplate O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,14) AND O.DELETED = 0 AND O.GUID IS NOT NULL
            GROUP BY O.GUID,O.OwnerID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                18 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN ClientClassYears O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,18) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                20 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN DocUploadPart O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,20) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                21 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN DiscussionGroups O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,21) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                22 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN JobBoards O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,22) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                25 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN GivingHistory O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,25) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                26 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN NewsReaders O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,26) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                27 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN NewsChannels O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,27) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                30 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN SiteStylesheets O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,30) AND O.GUID IS NOT NULL
            GROUP BY O.GUID
            UNION ALL
            SELECT 
                O.CalendarCategoryPicksID ObjectGuid,
                31 ObjectTypeID,
                CASE WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canView,
                CASE WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN CalendarEvtCategoryPicks O on O.CalendarCategoryPicksID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,31) AND O.CalendarCategoryPicksID IS NOT NULL
            GROUP BY O.CalendarCategoryPicksID
UNION ALL
SELECT 
                O.Guid ObjectGuid,
                33 ObjectTypeID,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canView,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN EmailProject O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,33) AND O.DELETED = 0 AND O.GUID IS NOT NULL
            GROUP BY O.GUID,O.OwnerID

            UNION ALL
            SELECT 
                O.Guid ObjectGuid,
                34 ObjectTypeID,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanView,1)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canView,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanEdit,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canEdit,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanAdd,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canAdd,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanDelete,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canDelete,
                CASE WHEN O.OwnerID=NULLIF(@ClientUsersID,0) THEN CONVERT(bit,1) WHEN SUM(coalesce(CanSecure,0)) > 0 THEN CONVERT(bit,1) 

ELSE CONVERT(bit,0) END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            RIGHT OUTER JOIN dbo.CustomFormElement O on O.GUID = OP.ObjectGUID 
            WHERE @ObjectTypeFilter IN (0,34) AND O.GUID IS NOT NULL AND O.TypeID = 1 
            GROUP BY O.GUID,O.OwnerID