Summary

Locate directly assigned user permissions, excluding designated folders such as home folder targets. Paths listed in the report’s File System Paths are excluded from the results.

Details

The query scope is enterprise-wide but allows designated folders to be excluded. Typical exclusions include home folder roots, where direct user assignments are expected.

Code

-- 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
    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