Users with Direct Folder Permissions in AD with Title

Details

Summary

Create a report to locate all of the directly assigned user permissions, excluding designated folders like home folder targets. This report is designed to work with version 4.1 and the File System Paths technology. The paths designated in the File System Paths list for this report will be excluded from the results.

Back Story

The customer has a number of shares where the trustees should only ever be assigned or granted to groups.

Information

The scope of the query is enterprize wide, but is designed to allow designated folders to be excluded. These exclutions might include home folder paths like home folders where a direct assignment is appropriate.

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

Author

rlagger

Post date

Monday, April 25, 2022 - 15:56

Last modified

Monday, April 25, 2022 - 15:56

Properties

Supported Version

4.1

Report Category

Permissions

Includes a Report Layout