-- This query finds all direct-user permission assignments to folders
-- for the entire collected data set for NTFS permissions, except for
-- areas defined by the injected tmp_cq_fs_paths construct.

-- The injected Target Paths (tmp_cq_fs_paths) in this case specify
-- an EXCLUSION list, meaning that the target paths defined for this
-- report, along with all their sub-folders, are EXCLUDED from the results
-- select * from #tmp_cq_fs_paths where is_permission_scan = 'true'

SELECT
    ntfs.fullpath,
    ntfs.trustee_display_name,
    adv.title as trustee_title,
    ntfs.basic_permissions,
    ntfs.access_mask,
    ntfs.access_mask_string,
    ntfs.ace_flags,
    ntfs.ace_flags_string,
    ntfs.ace_type,
    ntfs.ace_type_string,
    ntfs.server,
    ntfs.scan_target
FROM
    srs.current_ntfs_aces AS ntfs
LEFT JOIN #tmp_cq_fs_paths AS cq
  ON cq.scan_id = ntfs.scan_id
  AND cq.ns_left <= ntfs.ns_left
  AND cq.ns_right >= ntfs.ns_right
  AND cq.is_current = 'true'
  AND cq.is_permission_scan = 'true'
left join ad.ds_objects_view adv on adv.sam_principal_name = ntfs.trustee_display_name
WHERE cq.target_path IS NULL
  AND ntfs.path_type = 2
  AND ntfs.trustee_type = 1
  AND ntfs.ace_flags & 16 <> 16