Sharepoint SP_TrusteeAudit taking too long? Try this

Products Access Analyzer Show & Tell
1

Overview

SP_TrusteeAudit went from never finishing to 15 second completion

Description

Simple SQL query change with “realized views” or whatever they are called in 2025.

IF OBJECT_ID('tempdb..#SIDFilter', 'U') IS NOT NULL
    DROP TABLE #SIDFilter;

GO

SELECT [sids].[ObjectSid]
INTO #SIDFilter
FROM SA_SP_TrusteeAccess_Scope AS S
INNER JOIN (
    SELECT [uv].[NTAccount], [uv].[ObjectSid]
    FROM [dbo].[SA_ADInventory_UsersView] AS [uv]
    UNION ALL
    SELECT [gv].[NTAccount], [gv].[ObjectSid]
    FROM [dbo].[SA_ADInventory_GroupsView] AS [gv]
) AS sids ON [sids].[NTAccount] = S.[RowText];

IF EXISTS (
    SELECT *
    FROM [sys].[objects]
    WHERE object_id = OBJECT_ID(N'[dbo].SA_SP_TrusteeAudit_DirectPermissions')
      AND type IN (N'U')
)
BEGIN
    DROP TABLE SA_SP_TrusteeAudit_DirectPermissions;
    DROP TABLE SA_XX_SPAA_SiteCollectionsTraversalRealizedView;
    DROP TABLE SA_XX_SPAA_DirectPermissionsRealizedView;
END

SELECT *
INTO [SA_XX_SPAA_SiteCollectionsTraversalRealizedView]
FROM [dbo].[SA_SPAA_SiteCollectionsTraversalView] AS sctvv
WHERE sctvv.ResourceDeletedUSN IS NULL;

CREATE INDEX XX_SPAA_IX_SiteCollection
ON [dbo].[SA_XX_SPAA_SiteCollectionsTraversalRealizedView] ([HostId] ASC);

SELECT *
INTO [SA_XX_SPAA_DirectPermissionsRealizedView]
FROM [dbo].[SA_SPAA_DirectPermissionsView] AS dpvv
WHERE dpvv.[TrusteeType] = 4;

CREATE INDEX XX_SPAA_IX_DirectPermissionsHostID
ON [dbo].[SA_XX_SPAA_DirectPermissionsRealizedView] ([HostId] ASC);

CREATE INDEX XX_SPAA_IX_DirectPermissionsTrusteeNTStyleName
ON [dbo].[SA_XX_SPAA_DirectPermissionsRealizedView] ([TrusteeNTStyleName] ASC);

SELECT
    [DPV].[HostName] AS [Farm],
    [DPV].[ResourceName] AS [Resource Name],
    [DPV].[ResourceDescription] AS [Resource Type],
    [sctv].[FullPath] AS [URL],
    [DPV].[TrusteeNTStyleName] AS [NT Style Name],
    [DPV].[TrusteeDisplayName] AS [Trustee],
    [DPV].[RightsDescription] AS [Rights]
INTO SA_SP_TrusteeAudit_DirectPermissions
FROM [dbo].[SA_XX_SPAA_DirectPermissionsRealizedView] AS DPV WITH (NOLOCK)
INNER JOIN [dbo].[SA_XX_SPAA_SiteCollectionsTraversalRealizedView] AS sctv WITH (NOLOCK)
    ON [DPV].[HostId] = [sctv].[HostId]
INNER JOIN SA_SP_TrusteeAccess_Scope AS S
    ON S.[RowText] = DPV.[TrusteeNTStyleName];

SA_SP_TrusteeAudit_DirectPermissions.sql (2.9 KB)