Summary

Locate directly assigned user permissions within a particular NCP scan target.

Details

The query is scoped to NCP trustees within the desired scan target. Modify the scan_target value on line 16 to match your environment (for example, \\SERVER\VOLUME).

Code

SELECT
    q.fullpath,
    q.trustee_fdn,
    q.rights_string,
    q.t_count
FROM
    (SELECT ncp.trustee_fdn,
      ncp.rights_string,
      ncp.fullpath,
      ncp.trustee_type,
      ncp.scan_target,
      count(*) OVER (PARTITION BY ncp.trustee_fdn) AS t_count
    FROM srs.current_ncp_trustees AS ncp) AS q
WHERE
    (q.t_count >= 1) AND
    (q.scan_target LIKE ('\\QT-OES1\DATA') ESCAPE '#') AND
    (q.trustee_type IN (1, 2))
ORDER BY
    q.t_count DESC, q.trustee_fdn