UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS

Returns all ad-hoc query folders where a user has permission to that folder and all its ancestor folders.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS
            (
                @CURRENTAPPUSERID uniqueidentifier
            )
            returns table
            as
            return
            (
                with PERMISSIONEDFOLDERSCTE as
                (
                    select
                        ID,
                        PARENTFOLDERID,
                        NAME,
                        DISPLAYORDER
                    from dbo.ADHOCQUERYFOLDER
                    where 
                        (select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID) = 1 or 
                        dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYFOLDER_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERYFOLDER.ID) = 1
                ),
                RECURSIVECTE as
                (
                    select
                        ID, 
                        PARENTFOLDERID,
                        NAME,
                        DISPLAYORDER
                    from PERMISSIONEDFOLDERSCTE
                    where
                        PARENTFOLDERID is null

                    union all

                    select
                        PERMISSIONEDFOLDERSCTE.ID,
                        PERMISSIONEDFOLDERSCTE.PARENTFOLDERID,
                        PERMISSIONEDFOLDERSCTE.NAME,
                        PERMISSIONEDFOLDERSCTE.DISPLAYORDER
                    from PERMISSIONEDFOLDERSCTE
                    inner join RECURSIVECTE on PERMISSIONEDFOLDERSCTE.PARENTFOLDERID = RECURSIVECTE.ID
                )
                select
                    ID,
                    PARENTFOLDERID,
                    NAME,
                    DISPLAYORDER
                from RECURSIVECTE
            )