UFN_SYSTEMROLEAPPUSER_SITEPERMISSION

            CREATE function dbo.UFN_SYSTEMROLEAPPUSER_SITEPERMISSION
            (
                @APPUSERID uniqueidentifier
            )
            returns @SITEPERMISSION table (SYSTEMROLEID uniqueidentifier, SYSTEMROLENAME nvarchar(255), SITEID uniqueidentifier, SITENAME nvarchar(1024), HIERARCHYPATH hierarchyid, SYSTEMROLEAPPUSERID uniqueidentifier, APPUSERID uniqueidentifier) 
            with SCHEMABINDING
            as 
            begin                
                /*
                This function will only return site permissions when the security mode is set to access data based on specific sites.
                It is assumed that the caller will check for the IsSysAdmin and site security mode values outside of this function.
                */

                /*Walk down the branch for all selected sites*/
                with
                SITE_HIERACHY_CTE as
                (
                    select 
                        SITE.NAME, 
                        SITE.ID, 
                        PARENTSITE.ID as PARENTID, 
                        SITE.HIERARCHYPATH 
                    from 
                        dbo.SITE 
                        inner join dbo.SITE as PARENTSITE on PARENTSITE.HIERARCHYPATH = SITE.HIERARCHYPATH.GetAncestor(1)
                ),
                ASSIGNED_SITES_CTE as
                (
                    select 
                        SYSTEMROLE.ID as SYSTEMROLEID,
                        SYSTEMROLE.NAME as SYSTEMROLENAME,
                        SITE.ID as SITEID,
                        SITE.NAME as SITENAME,
                        SITE.HIERARCHYPATH,
                        SYSTEMROLEAPPUSER.ID as SYSTEMROLEAPPUSERID,
                        SYSTEMROLEAPPUSER.APPUSERID
                    from 
                        dbo.SYSTEMROLEAPPUSERSITE
                        inner join dbo.SITE on SITE.ID = SYSTEMROLEAPPUSERSITE.SITEID 
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.ID = SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID 
                        inner join dbo.SYSTEMROLE on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID 
                    where
                        (SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID OR @APPUSERID is null) and
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 2
                union all
                    select 
                        ASSIGNED_SITES_CTE.SYSTEMROLEID, 
                        ASSIGNED_SITES_CTE.SYSTEMROLENAME,
                        SITE_HIERACHY_CTE.ID, 
                        SITE_HIERACHY_CTE.NAME,
                        SITE_HIERACHY_CTE.HIERARCHYPATH,
                        ASSIGNED_SITES_CTE.SYSTEMROLEAPPUSERID,
                        ASSIGNED_SITES_CTE.APPUSERID
                    from 
                        ASSIGNED_SITES_CTE
                        inner join SITE_HIERACHY_CTE on SITE_HIERACHY_CTE.PARENTID = ASSIGNED_SITES_CTE.SITEID
                )
                insert into @SITEPERMISSION(SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, HIERARCHYPATH, SYSTEMROLEAPPUSERID, APPUSERID) 
                    select SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, HIERARCHYPATH, SYSTEMROLEAPPUSERID, APPUSERID from ASSIGNED_SITES_CTE;

                /*Walk down the hierarchy branch of the selected branch site*/
                with
                SITE_HIERACHY_CTE as
                (
                    select 
                        SITE.NAME, 
                        SITE.ID, 
                        PARENTSITE.ID as PARENTID, 
                        SITE.HIERARCHYPATH 
                    from 
                        dbo.SITE 
                        inner join dbo.SITE as PARENTSITE on PARENTSITE.HIERARCHYPATH = SITE.HIERARCHYPATH.GetAncestor(1)
                ),
                ASSIGNED_SITES_CTE as
                (
                    select 
                        SYSTEMROLE.ID as SYSTEMROLEID,
                        SYSTEMROLE.NAME as SYSTEMROLENAME,
                        SITE.ID as SITEID,
                        SITE.NAME as SITENAME,
                        SITE.HIERARCHYPATH,
                        SYSTEMROLEAPPUSER.ID as SYSTEMROLEAPPUSERID,
                        SYSTEMROLEAPPUSER.APPUSERID
                    from 
                        dbo.SYSTEMROLEAPPUSER
                        inner join dbo.SITE on SITE.ID = SYSTEMROLEAPPUSER.BRANCHSITEID
                        inner join dbo.SYSTEMROLE on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID 
                    where
                        (SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID OR @APPUSERID is null) and
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 3
                union all
                    select 
                        ASSIGNED_SITES_CTE.SYSTEMROLEID, 
                        ASSIGNED_SITES_CTE.SYSTEMROLENAME,
                        SITE_HIERACHY_CTE.ID, 
                        SITE_HIERACHY_CTE.NAME,
                        SITE_HIERACHY_CTE.HIERARCHYPATH,
                        ASSIGNED_SITES_CTE.SYSTEMROLEAPPUSERID,
                        ASSIGNED_SITES_CTE.APPUSERID
                    from 
                        ASSIGNED_SITES_CTE
                        inner join SITE_HIERACHY_CTE on SITE_HIERACHY_CTE.PARENTID = ASSIGNED_SITES_CTE.SITEID
                )
                insert into @SITEPERMISSION(SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, HIERARCHYPATH, SYSTEMROLEAPPUSERID, APPUSERID) 
                    select SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, HIERARCHYPATH, SYSTEMROLEAPPUSERID, APPUSERID from ASSIGNED_SITES_CTE;


                /*Walk up the hierarchy branch of the selected branch site*/
                with BRANCH_CTE as 
                (
                    select 
                        SYSTEMROLE.ID as SYSTEMROLEID,
                        SYSTEMROLE.NAME as SYSTEMROLENAME,
                        PARENTSITE.ID as SITEID,
                        PARENTSITE.NAME as SITENAME,
                        PARENTSITE.HIERARCHYPATH,
                        SYSTEMROLEAPPUSER.ID as SYSTEMROLEAPPUSERID,
                        SYSTEMROLEAPPUSER.APPUSERID,
                        PARENTSITE.HIERARCHYPATH.GetAncestor(1) as PARENTHIERARCHYID
                    from 
                        dbo.SYSTEMROLEAPPUSER
                        inner join dbo.SITE on SITE.ID = SYSTEMROLEAPPUSER.BRANCHSITEID 
                        inner join dbo.SYSTEMROLE on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLE.ID 
                        left outer join dbo.SITE as PARENTSITE on PARENTSITE.HIERARCHYPATH = SITE.HIERARCHYPATH.GetAncestor(1) 
                    where
                        (SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID OR @APPUSERID is null) and
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 3 and
                        SITE.HIERARCHYPATH.GetAncestor(1) <> hierarchyid::GetRoot()
                union all
                    select 
                        BRANCH_CTE.SYSTEMROLEID,
                        BRANCH_CTE.SYSTEMROLENAME,
                        SITE.ID,
                        SITE.NAME,
                        SITE.HIERARCHYPATH,
                        BRANCH_CTE.SYSTEMROLEAPPUSERID,
                        BRANCH_CTE.APPUSERID,
                        SITE.HIERARCHYPATH.GetAncestor(1)
                    from
                        BRANCH_CTE
                        inner join dbo.SITE on SITE.HIERARCHYPATH = BRANCH_CTE.PARENTHIERARCHYID
                    where
                        BRANCH_CTE.PARENTHIERARCHYID <> hierarchyid::GetRoot()
                ) 
                    insert into @SITEPERMISSION(SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, HIERARCHYPATH, SYSTEMROLEAPPUSERID, APPUSERID) 
                        select SYSTEMROLEID, SYSTEMROLENAME, SITEID, SITENAME, HIERARCHYPATH, SYSTEMROLEAPPUSERID, APPUSERID from BRANCH_CTE 

                return
            end