MS365 Permissions by Site

MS365 Permissions by Site

Details

Details

Summary

New with version 4.0 is ability to scan and report on permissions and sharing within Microsoft 365. Know how the permissions are set and how files are sharing can make a large impact on how well a company’s data is protected.

This report comes in a couple of formats. First a custom paginated format and secondly a CSV based format.

NOTE: Due to schema changes in 4.1, be sure to download the appropriate version for your installation.

Code
 --
-- To filter on sites, only uncomment the WHERE filter for s.web_url
-- To filter by drives, only uncomment the WHERE filter for d.web_url
-- For each filter, one or more paths may be entered separated by commas
-- NOTE:  When filtering by sites, do not include a trailing slash
--        except for the default tenant site
--        Default site example:   https://mytenant.sharepoint.com/
--        Other sites example:    https://mytenant.sharepoint.com/Engineering
--

SELECT
    p.is_inherited,
    s.web_url AS site_path,
    s.display_name AS site_display_name,
    s.name AS site_name,
    s.ms365_id as site_ms365_id,
    d.web_url AS drive_path,
    d.name AS drive_name,
    RIGHT(pp.web_url, LEN(pp.web_url) - LEN(d.web_url)) AS parent_path,
    di.name AS item_name,
    dit.item_type_name AS item_type,
    CASE
        WHEN p.link_scope IS NOT NULL THEN 'Sharing Link'
        ELSE 'Direct'
    END AS permission_type,
    CASE
        WHEN p.link_scope = 'anonymous' THEN '[Anonymous]'
        WHEN p.link_scope = 'organization' THEN '[Organization]'
        WHEN slm.display_name IS NOT NULL THEN slm.display_name
        ELSE p.grantedto_display_name
    END AS trustee_name,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN u2.ms365_id IS NOT NULL THEN u2.upn
        WHEN u.ms365_id IS NOT NULL THEN u.upn
        ELSE NULL
    END AS trustee_upn,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN slm.ms365_id IS NOT NULL THEN slm.ms365_id
        ELSE p.grantedto_ms365_id
    END AS trustee_ms365_id,
    CASE
        WHEN g.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g.group_types IS NOT NULL THEN 'Security'
        WHEN g2.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g2.group_types IS NOT NULL THEN 'Security'
        ELSE NULL
    END AS trustee_group_type,
    p.roles AS permission,
    p.link_scope,
    p.link_type,
    CASE p.link_prevents_download
        WHEN 'true' THEN 'false'
        ELSE 'true'
    END AS can_download,
    --p.is_inherited,
    CASE p.has_password
        WHEN 'true' THEN 'true'
        ELSE 'false'
    END AS has_password,
    p.expire_time,
    CASE
        WHEN odu.display_name IS NOT NULL THEN odu.display_name
        WHEN tm.display_name IS NOT NULL THEN tm.display_name
        ELSE NULL
    END AS associated_entity_name,
    CASE
        WHEN odu.upn IS NOT NULL THEN odu.upn
        WHEN tm.ms365_id IS NOT NULL THEN tm.ms365_id
        ELSE NULL
    END AS associated_entity_id,
    CASE
        WHEN odu.ms365_id IS NOT NULL THEN 'User'
        WHEN tm.ms365_id IS NOT NULL THEN 'Team'
        ELSE NULL
    END AS associated_entity_type
FROM ms365.drives AS d
JOIN ms365.site_drives AS sd ON sd.ms365_drive_id = d.ms365_id
JOIN ms365.sites AS s ON s.ms365_id = sd.ms365_site_id
JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state = 1
JOIN ms365.drive_items AS di ON di.scan_id = ds.id
LEFT JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = di.scan_id
JOIN ms365.permissions AS p ON p.drive_item_id = di.id
LEFT JOIN ms365.sharing_link_members AS slm ON slm.permission_id = p.id
LEFT JOIN ms365.users AS u ON u.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.groups AS g ON g.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.users AS u2 ON u2.ms365_id = slm.ms365_id
LEFT JOIN ms365.groups AS g2 ON g2.ms365_id = slm.ms365_id
LEFT JOIN ms365.drive_item_types AS dit ON dit.item_type = di.item_type
LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id AND gdm.tenant_id = d.tenant_id
LEFT JOIN ms365.teams AS tm ON tm.ms365_id = gdm.ms365_group_id AND tm.tenant_id = gdm.tenant_id
LEFT JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id AND udm.tenant_id = d.tenant_id
LEFT JOIN ms365.users AS odu ON odu.ms365_id = udm.ms365_user_id AND odu.tenant_id = udm.tenant_id
WHERE 1=1
  AND (p.is_inherited = 'false' OR pp.ms365_parent_id = '')
  AND (p.roles IS NOT NULL AND p.roles <> '')
  --AND s.web_url IN ('https://mytenant.sharepoint.com/')
  --AND d.web_url IN ('https://mytenant-my.sharepoint.com/personal/cgeorge_corp_mytent_org/Documents') 
 --
-- To filter on sites, only uncomment the WHERE filter for s.web_url
-- To filter by drives, only uncomment the WHERE filter for d.web_url
-- For each filter, one or more paths may be entered separated by commas
-- NOTE:  When filtering by sites, do not include a trailing slash
--        except for the default tenant site
--        Default site example:   https://mytenant.sharepoint.com/
--        Other sites example:    https://mytenant.sharepoint.com/Engineering
--

SELECT
    p.is_inherited,
    s.web_url AS site_path,
    s.display_name AS site_display_name,
    s.name AS site_name,
    s.ms365_id as site_ms365_id,
    d.web_url AS drive_path,
    d.name AS drive_name,
    RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path,
    di.name AS item_name,
    dit.item_type_name AS item_type,
    CASE
        WHEN p.link_scope IS NOT NULL THEN 'Sharing Link'
        ELSE 'Direct'
    END AS permission_type,
    CASE
        WHEN p.link_scope = 'anonymous' THEN '[Anonymous]'
        WHEN p.link_scope = 'organization' THEN '[Organization]'
        WHEN slm.display_name IS NOT NULL THEN slm.display_name
        ELSE p.grantedto_display_name
    END AS trustee_name,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN u2.ms365_id IS NOT NULL THEN u2.upn
        WHEN u.ms365_id IS NOT NULL THEN u.upn
        ELSE NULL
    END AS trustee_upn,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN slm.ms365_id IS NOT NULL THEN slm.ms365_id
        ELSE p.grantedto_ms365_id
    END AS trustee_ms365_id,
    CASE
        WHEN g.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g.group_types IS NOT NULL THEN 'Security'
        WHEN g2.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g2.group_types IS NOT NULL THEN 'Security'
        ELSE NULL
    END AS trustee_group_type,
    p.roles AS permission,
    p.link_scope,
    p.link_type,
    CASE p.link_prevents_download
        WHEN 'true' THEN 'false'
        ELSE 'true'
    END AS can_download,
    --p.is_inherited,
    CASE p.has_password
        WHEN 'true' THEN 'true'
        ELSE 'false'
    END AS has_password,
    p.expire_time,
    CASE
        WHEN odu.display_name IS NOT NULL THEN odu.display_name
        WHEN tm.display_name IS NOT NULL THEN tm.display_name
        ELSE NULL
    END AS associated_entity_name,
    CASE
        WHEN odu.upn IS NOT NULL THEN odu.upn
        WHEN tm.ms365_id IS NOT NULL THEN tm.ms365_id
        ELSE NULL
    END AS associated_entity_id,
    CASE
        WHEN odu.ms365_id IS NOT NULL THEN 'User'
        WHEN tm.ms365_id IS NOT NULL THEN 'Team'
        ELSE NULL
    END AS associated_entity_type
FROM ms365.drives AS d
JOIN ms365.site_drives AS sd ON sd.ms365_drive_id = d.ms365_id
JOIN ms365.sites AS s ON s.ms365_id = sd.ms365_site_id
JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state = 1
JOIN ms365.drive_items AS di ON di.scan_id = ds.id
LEFT JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = di.scan_id
JOIN ms365.permissions AS p ON p.drive_item_id = di.id
LEFT JOIN ms365.sharing_link_members AS slm ON slm.permission_id = p.id
LEFT JOIN ms365.users AS u ON u.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.groups AS g ON g.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.users AS u2 ON u2.ms365_id = slm.ms365_id
LEFT JOIN ms365.groups AS g2 ON g2.ms365_id = slm.ms365_id
LEFT JOIN ms365.drive_item_types AS dit ON dit.item_type = di.item_type
LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id AND gdm.tenant_id = d.tenant_id
LEFT JOIN ms365.teams AS tm ON tm.ms365_id = gdm.ms365_group_id AND tm.tenant_id = gdm.tenant_id
LEFT JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id AND udm.tenant_id = d.tenant_id
LEFT JOIN ms365.users AS odu ON odu.ms365_id = udm.ms365_user_id AND odu.tenant_id = udm.tenant_id
WHERE 1=1
  AND (p.is_inherited = 'false' OR pp.ms365_parent_id = '')
  AND (p.roles IS NOT NULL AND p.roles <> '')
  --AND s.web_url IN ('https://mytenant.sharepoint.com/')
  --AND d.web_url IN ('https://mytenant-my.sharepoint.com/personal/cgeorge_corp_mytent_org/Documents') 
 --
-- To filter on sites, only uncomment the WHERE filter for s.web_url
-- To filter by drives, only uncomment the WHERE filter for d.web_url
-- For each filter, one or more paths may be entered separated by commas
-- NOTE:  When filtering by sites, do not include a trailing slash
--        except for the default tenant site
--        Default site example:   https://mytenant.sharepoint.com/
--        Other sites example:    https://mytenant.sharepoint.com/Engineering
--

SELECT
    p.is_inherited,
    s.web_url AS site_path,
    s.display_name AS site_display_name,
    s.name AS site_name,
    s.ms365_id as site_ms365_id,
    d.web_url AS drive_path,
    d.name AS drive_name,
    RIGHT(pp.web_url, LEN(pp.web_url) - LEN(d.web_url)) AS parent_path,
    di.name AS item_name,
    dit.item_type_name AS item_type,
    CASE
        WHEN p.link_scope IS NOT NULL THEN 'Sharing Link'
        ELSE 'Direct'
    END AS permission_type,
    CASE
        WHEN p.link_scope = 'anonymous' THEN '[Anonymous]'
        WHEN p.link_scope = 'organization' THEN '[Organization]'
        WHEN slm.display_name IS NOT NULL THEN slm.display_name
        ELSE p.grantedto_display_name
    END AS trustee_name,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN u2.ms365_id IS NOT NULL THEN u2.upn
        WHEN u.ms365_id IS NOT NULL THEN u.upn
        ELSE NULL
    END AS trustee_upn,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN slm.ms365_id IS NOT NULL THEN slm.ms365_id
        ELSE p.grantedto_ms365_id
    END AS trustee_ms365_id,
    CASE
        WHEN g.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g.group_types IS NOT NULL THEN 'Security'
        WHEN g2.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g2.group_types IS NOT NULL THEN 'Security'
        ELSE NULL
    END AS trustee_group_type,
    p.roles AS permission,
    p.link_scope,
    p.link_type,
    CASE p.link_prevents_download
        WHEN 'true' THEN 'false'
        ELSE 'true'
    END AS can_download,
    --p.is_inherited,
    CASE p.has_password
        WHEN 'true' THEN 'true'
        ELSE 'false'
    END AS has_password,
    p.expire_time,
    CASE
        WHEN odu.display_name IS NOT NULL THEN odu.display_name
        WHEN tm.display_name IS NOT NULL THEN tm.display_name
        ELSE NULL
    END AS associated_entity_name,
    CASE
        WHEN odu.upn IS NOT NULL THEN odu.upn
        WHEN tm.ms365_id IS NOT NULL THEN tm.ms365_id
        ELSE NULL
    END AS associated_entity_id,
    CASE
        WHEN odu.ms365_id IS NOT NULL THEN 'User'
        WHEN tm.ms365_id IS NOT NULL THEN 'Team'
        ELSE NULL
    END AS associated_entity_type
FROM ms365.drives AS d
JOIN ms365.site_drives AS sd ON sd.ms365_drive_id = d.ms365_id
JOIN ms365.sites AS s ON s.ms365_id = sd.ms365_site_id
JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state = 1
JOIN ms365.drive_items AS di ON di.scan_id = ds.id
LEFT JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = di.scan_id
JOIN ms365.permissions AS p ON p.drive_item_id = di.id
LEFT JOIN ms365.sharing_link_members AS slm ON slm.permission_id = p.id
LEFT JOIN ms365.users AS u ON u.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.groups AS g ON g.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.users AS u2 ON u2.ms365_id = slm.ms365_id
LEFT JOIN ms365.groups AS g2 ON g2.ms365_id = slm.ms365_id
LEFT JOIN ms365.drive_item_types AS dit ON dit.item_type = di.item_type
LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id AND gdm.tenant_id = d.tenant_id
LEFT JOIN ms365.teams AS tm ON tm.ms365_id = gdm.ms365_group_id AND tm.tenant_id = gdm.tenant_id
LEFT JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id AND udm.tenant_id = d.tenant_id
LEFT JOIN ms365.users AS odu ON odu.ms365_id = udm.ms365_user_id AND odu.tenant_id = udm.tenant_id
WHERE 1=1
  AND (p.is_inherited = 'false' OR pp.ms365_parent_id = '')
  AND (p.roles IS NOT NULL AND p.roles <> '')
  --AND s.web_url IN ('https://mytenant.sharepoint.com/')
  --AND d.web_url IN ('https://mytenant-my.sharepoint.com/personal/cgeorge_corp_mytent_org/Documents') 
 --
-- To filter on sites, only uncomment the WHERE filter for s.web_url
-- To filter by drives, only uncomment the WHERE filter for d.web_url
-- For each filter, one or more paths may be entered separated by commas
-- NOTE:  When filtering by sites, do not include a trailing slash
--        except for the default tenant site
--        Default site example:   https://mytenant.sharepoint.com/
--        Other sites example:    https://mytenant.sharepoint.com/Engineering
--

SELECT
    p.is_inherited,
    s.web_url AS site_path,
    s.display_name AS site_display_name,
    s.name AS site_name,
    s.ms365_id as site_ms365_id,
    d.web_url AS drive_path,
    d.name AS drive_name,
    RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path,
    di.name AS item_name,
    dit.item_type_name AS item_type,
    CASE
        WHEN p.link_scope IS NOT NULL THEN 'Sharing Link'
        ELSE 'Direct'
    END AS permission_type,
    CASE
        WHEN p.link_scope = 'anonymous' THEN '[Anonymous]'
        WHEN p.link_scope = 'organization' THEN '[Organization]'
        WHEN slm.display_name IS NOT NULL THEN slm.display_name
        ELSE p.grantedto_display_name
    END AS trustee_name,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN u2.ms365_id IS NOT NULL THEN u2.upn
        WHEN u.ms365_id IS NOT NULL THEN u.upn
        ELSE NULL
    END AS trustee_upn,
    CASE
        WHEN p.link_scope IN ('anonymous', 'organization') THEN NULL
        WHEN slm.ms365_id IS NOT NULL THEN slm.ms365_id
        ELSE p.grantedto_ms365_id
    END AS trustee_ms365_id,
    CASE
        WHEN g.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g.group_types IS NOT NULL THEN 'Security'
        WHEN g2.group_types LIKE '%"Unified"%' THEN 'MS365'
        WHEN g2.group_types IS NOT NULL THEN 'Security'
        ELSE NULL
    END AS trustee_group_type,
    p.roles AS permission,
    p.link_scope,
    p.link_type,
    CASE p.link_prevents_download
        WHEN 'true' THEN 'false'
        ELSE 'true'
    END AS can_download,
    --p.is_inherited,
    CASE p.has_password
        WHEN 'true' THEN 'true'
        ELSE 'false'
    END AS has_password,
    p.expire_time,
    CASE
        WHEN odu.display_name IS NOT NULL THEN odu.display_name
        WHEN tm.display_name IS NOT NULL THEN tm.display_name
        ELSE NULL
    END AS associated_entity_name,
    CASE
        WHEN odu.upn IS NOT NULL THEN odu.upn
        WHEN tm.ms365_id IS NOT NULL THEN tm.ms365_id
        ELSE NULL
    END AS associated_entity_id,
    CASE
        WHEN odu.ms365_id IS NOT NULL THEN 'User'
        WHEN tm.ms365_id IS NOT NULL THEN 'Team'
        ELSE NULL
    END AS associated_entity_type
FROM ms365.drives AS d
JOIN ms365.site_drives AS sd ON sd.ms365_drive_id = d.ms365_id
JOIN ms365.sites AS s ON s.ms365_id = sd.ms365_site_id
JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state = 1
JOIN ms365.drive_items AS di ON di.scan_id = ds.id
LEFT JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = di.scan_id
JOIN ms365.permissions AS p ON p.drive_item_id = di.id
LEFT JOIN ms365.sharing_link_members AS slm ON slm.permission_id = p.id
LEFT JOIN ms365.users AS u ON u.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.groups AS g ON g.ms365_id = p.grantedto_ms365_id
LEFT JOIN ms365.users AS u2 ON u2.ms365_id = slm.ms365_id
LEFT JOIN ms365.groups AS g2 ON g2.ms365_id = slm.ms365_id
LEFT JOIN ms365.drive_item_types AS dit ON dit.item_type = di.item_type
LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id AND gdm.tenant_id = d.tenant_id
LEFT JOIN ms365.teams AS tm ON tm.ms365_id = gdm.ms365_group_id AND tm.tenant_id = gdm.tenant_id
LEFT JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id AND udm.tenant_id = d.tenant_id
LEFT JOIN ms365.users AS odu ON odu.ms365_id = udm.ms365_user_id AND odu.tenant_id = udm.tenant_id
WHERE 1=1
  AND (p.is_inherited = 'false' OR pp.ms365_parent_id = '')
  AND (p.roles IS NOT NULL AND p.roles <> '')
  --AND s.web_url IN ('https://mytenant.sharepoint.com/')
  --AND d.web_url IN ('https://mytenant-my.sharepoint.com/personal/cgeorge_corp_mytent_org/Documents')
Author
rlagger
Post date
Thursday, April 8, 2021 - 14:51
Last modified
Friday, April 26, 2024 - 12:49
Properties
Supported Version
4.1
4.0
Report Category
Permissions
Microsoft 365
Includes a Report Layout
Yes
Databases
SQL Server
PostgreSQL
Tags
Microsoft 365
Downloads
AttachmentSize
MS365-Permissions-by-Site.zip (Version 4.0 only)9.84 KB
MS365-Permissions-by-Site-CSV.zip (Version 4.0 only)2.75 KB
MS365-Permissions-by-Site-CSV-4.1.zip (Version 4.1)3.04 KB
MS365-Permissions-by-Site-4.1.zip (Version 4.1)9.94 KB
Sample Report
AttachmentSize
MS365-Permissions-by-Site.pdf268.65 KB
MS365-Permissions-by-Site-CSV-20210402195218.csv2.25 KB