SQL Server Security Report: Who Has sysadmin, and What Changed

SQL Server Auditing: SQL Server Security Report: Who Has sysadmin, and What Changed

Applies to SQL Server 2019, 2022 and 2025. The script runs in Windows PowerShell 5.1 or PowerShell 7.

The two security questions auditors always ask are “who has sysadmin?” and “who changed it?”. SQL Server doesn’t keep a history of either unless you’ve set up auditing. This report takes a snapshot of every instance each run and compares it with the one before, so a new sysadmin shows up the next morning instead of at the annual audit.

What the report shows

  • What changed since the previous run: role members and high permissions added or removed, logins added, removed, enabled, disabled or changed. Sysadmin changes come first.
  • Who has sysadmin or equivalent today: members of sysadmin and securityadmin, and anyone granted CONTROL SERVER, IMPERSONATE ANY LOGIN or ALTER ANY LOGIN directly.
  • Members per role across the estate, as one line each for the monthly pack.
  • Login problems: a password that’s blank or the same as the login name, password policy turned off, SQL login passwords older than a year, sa enabled, and SQL logins with sysadmin.
  • Orphaned users in every database, with the statement that fixes each one.
  • Instances the audit couldn’t reach.

Why it matters

Sysadmin access is the keys to everything: every database, every backup, and usually the operating system too. Most estates have more of it than anyone would sign off, granted for a project or an incident and never taken back.

The change list is the useful part day to day. Every line should match a change ticket. If it doesn’t, someone needs to ask why, and the sooner the better.

The weak password check catches the two passwords that turn up again and again: blank, and the same as the login name. It uses PWDCOMPARE against the stored hash on the server, so no password ever leaves SQL Server or appears in a file.

Run it daily or weekly; look at the change list after each run and send the full report monthly to security and audit.

The script

For each instance, one query returns three result sets: server role members and high permissions, every login, and orphaned users from each online database it can open. They’re stored with the same capture date, which is what lets the report compare this run with the last.

# Invoke-SecurityAudit.ps1
# For every instance in the list:
#   - members of every fixed server role (sysadmin first in the report), plus
#     logins granted CONTROL SERVER, ALTER ANY LOGIN, IMPERSONATE ANY LOGIN or
#     ALTER ANY DATABASE directly
#   - every login: type, disabled, sysadmin, password policy, password age and
#     the two classic weak passwords (blank, or the same as the login name)
#   - orphaned SQL users in every online database, with the fix statement
# Schedule it daily or weekly; the report shows what changed since the run before.
#
# Permissions: the account needs CONTROL SERVER (or sysadmin) to read password
# hashes for the weak-password check, and access to each database for orphans.
# Without them those columns come back empty; the rest still works.
#
# Tables: run sql\01_Create_Tables.sql first.
# Report: sql\SecurityReport.sql

# ---- Settings ---------------------------------------------------------------
$ServerListPath    = 'C:\DBA\Servers.txt'
$InventoryServer   = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------

. "$PSScriptRoot\DbaReportHelpers.ps1"

# Three result sets: role members and high permissions, logins, orphaned users
$SecurityQuery = @"
SET NOCOUNT ON;

SELECT
    r.name COLLATE DATABASE_DEFAULT AS RoleOrPermission,   -- the two halves differ in collation
    p.name                  AS MemberName,
    p.type_desc             AS MemberType,
    p.is_disabled           AS IsDisabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals   AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals   AS p ON p.principal_id = rm.member_principal_id
WHERE p.name NOT LIKE N'##%'
UNION ALL
SELECT
    N'PERMISSION: ' + pe.permission_name COLLATE DATABASE_DEFAULT,
    p.name,
    p.type_desc,
    p.is_disabled
FROM sys.server_permissions AS pe
JOIN sys.server_principals  AS p ON p.principal_id = pe.grantee_principal_id
WHERE pe.state IN ('G', 'W')
  AND pe.permission_name IN (N'CONTROL SERVER', N'ALTER ANY LOGIN', N'IMPERSONATE ANY LOGIN', N'ALTER ANY DATABASE')
  AND p.name NOT LIKE N'##%';

SELECT
    p.name                                                     AS LoginName,
    p.type_desc                                                AS LoginType,
    p.is_disabled                                              AS IsDisabled,
    CAST(CASE WHEN EXISTS (SELECT 1
                           FROM sys.server_role_members AS rm
                           WHERE rm.member_principal_id = p.principal_id
                             AND rm.role_principal_id = (SELECT principal_id FROM sys.server_principals
                                                         WHERE name = N'sysadmin' AND type = 'R'))
              THEN 1 ELSE 0 END AS BIT)                        AS IsSysadmin,
    p.create_date                                              AS CreateDate,
    p.modify_date                                              AS ModifyDate,
    p.default_database_name                                    AS DefaultDatabase,
    CAST(LOGINPROPERTY(p.name, 'PasswordLastSetTime') AS DATETIME2(0)) AS PasswordLastSet,
    l.is_policy_checked                                        AS IsPolicyChecked,
    l.is_expiration_checked                                    AS IsExpirationChecked,
    CASE WHEN l.password_hash IS NULL                        THEN NULL
         WHEN PWDCOMPARE(p.name, l.password_hash) = 1        THEN N'Password = login name'
         WHEN PWDCOMPARE(N'', l.password_hash) = 1           THEN N'Blank password'
    END                                                        AS WeakPassword
FROM sys.server_principals AS p
LEFT JOIN sys.sql_logins   AS l ON l.principal_id = p.principal_id
WHERE p.type IN ('S', 'U', 'G', 'E', 'X')
  AND p.name NOT LIKE N'##%';

CREATE TABLE #Orphans
(
    DatabaseName      SYSNAME       NOT NULL,
    UserName          SYSNAME       NOT NULL,
    LoginWithSameName SYSNAME       NULL,
    FixStatement      NVARCHAR(600) NULL
);

DECLARE @Name SYSNAME, @Sql NVARCHAR(MAX);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE' AND HAS_DBACCESS(name) = 1;

OPEN dbs;
FETCH NEXT FROM dbs INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'USE ' + QUOTENAME(@Name) + N';
INSERT INTO #Orphans (DatabaseName, UserName, LoginWithSameName, FixStatement)
SELECT DB_NAME(), u.name, sp.name,
       CASE WHEN sp.name IS NOT NULL
            THEN N''USE '' + QUOTENAME(DB_NAME()) + N''; ALTER USER '' + QUOTENAME(u.name)
                 + N'' WITH LOGIN = '' + QUOTENAME(sp.name) + N'';''
            ELSE N''-- No login called '' + u.name
                 + N'': recreate it with the original SID, or drop the user if it is no longer needed''
       END
FROM sys.database_principals AS u
LEFT JOIN sys.server_principals AS sp
       ON sp.name = u.name COLLATE DATABASE_DEFAULT
WHERE u.type = ''S''
  AND u.authentication_type = 1          -- instance (login-based) users, not contained users
  AND u.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
  AND NOT EXISTS (SELECT 1 FROM sys.server_principals AS s WHERE s.sid = u.sid);';

    BEGIN TRY
        EXEC sys.sp_executesql @Sql;
    END TRY
    BEGIN CATCH
        PRINT 'Skipped ' + @Name + ': ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM dbs INTO @Name;
END;

CLOSE dbs;
DEALLOCATE dbs;

SELECT DatabaseName, UserName, LoginWithSameName, FixStatement
FROM #Orphans;
"@

$Servers     = Get-ServerList -Path $ServerListPath
$CaptureDate = Get-Date
$Repo        = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase

try {
    Assert-InventoryTable $Repo 'dbo.CollectionLog', 'dbo.SecurityServerRoleMember', 'dbo.SecurityLogin', 'dbo.SecurityOrphanedUser'

    foreach ($Server in $Servers) {
        try {
            $Source = Open-SqlConnection -Server $Server
            try {
                $Set = Invoke-SqlQuerySet $Source $SecurityQuery
            }
            finally {
                $Source.Close()
            }

            $Members = Add-DataTableRows $Repo 'dbo.SecurityServerRoleMember' $Set.Tables[0] $Server $CaptureDate
            $Logins  = Add-DataTableRows $Repo 'dbo.SecurityLogin'            $Set.Tables[1] $Server $CaptureDate
            $Orphans = Add-DataTableRows $Repo 'dbo.SecurityOrphanedUser'     $Set.Tables[2] $Server $CaptureDate

            Write-CollectionLog $Repo -Collection 'Security' -ServerName $Server -CaptureDate $CaptureDate `
                                -Status 'SUCCESS' -Rows $Logins
            Write-Host "$Server : $Members role members, $Logins logins, $Orphans orphaned users"
        }
        catch {
            Write-CollectionLog $Repo -Collection 'Security' -ServerName $Server -CaptureDate $CaptureDate `
                                -Status 'FAILED' -ErrorMessage $_.Exception.Message
            Write-Warning "$Server : $($_.Exception.Message)"
        }
    }
}
finally {
    $Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script). This version adds the functions the newer collectors use; it replaces the older copy and the earlier scripts still work with it.
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
#     . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.

function Get-ServerList {
    # One server per line. Blank lines and anything after # are ignored,
    # and duplicates (in any case) are removed.
    param([Parameter(Mandatory)][string]$Path)

    Get-Content -Path $Path |
        ForEach-Object { ($_ -split '#')[0].Trim() } |
        Where-Object { $_ } |
        Sort-Object -Unique
}

function Get-ComputerName {
    # SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
    param([Parameter(Mandatory)][string]$Server)
    ($Server -split '\\')[0]
}

function Open-SqlConnection {
    param(
        [Parameter(Mandatory)][string]$Server,
        [string]$Database = 'master'
    )

    $Conn = New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
                             "TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
    $Conn.Open()
    $Conn
}

function Invoke-SqlQuery {
    # Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Table = New-Object System.Data.DataTable
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
    , $Table
}

function Invoke-SqlNonQuery {
    param($Connection, [string]$Sql)

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    $null = $Cmd.ExecuteNonQuery()
}

function Add-InventoryRow {
    # Inserts one row from an ordered hashtable of column = value.
    # Every value goes in as a parameter, never pasted into the SQL text.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)][System.Collections.IDictionary]$Row
    )

    $Columns = @($Row.Keys)
    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
                       (($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'

    foreach ($Column in $Columns) {
        $Value = $Row[$Column]
        if ($null -eq $Value -or $Value -is [System.DBNull] -or
            ($Value -is [string] -and $Value.Trim() -eq '')) {
            $Value = [System.DBNull]::Value
        }
        $null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
    }
    $null = $Cmd.ExecuteNonQuery()
}

function Invoke-SqlQuerySet {
    # Like Invoke-SqlQuery, but returns a DataSet: one DataTable per result set.
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 600
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Set = New-Object System.Data.DataSet
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Set)
    $Set
}

function Assert-InventoryTable {
    # Stops the run early with a clear message if 01_Create_Tables.sql hasn't been run.
    param($Connection, [Parameter(Mandatory)][string[]]$Table)

    foreach ($Name in $Table) {
        $Id = (Invoke-SqlQuery $Connection "SELECT OBJECT_ID(@Name, 'U') AS Id" @{ Name = $Name }).Rows[0].Id
        if ($Id -is [System.DBNull]) {
            throw "Table $Name is missing. Run sql\01_Create_Tables.sql in the DBA database first."
        }
    }
}

function Add-DataTableRows {
    # Inserts every row of a DataTable, adding the run's CaptureDate and ServerName. Returns the row count.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)]$Data,
        [Parameter(Mandatory)][string]$ServerName,
        [Parameter(Mandatory)][datetime]$CaptureDate
    )

    foreach ($Source in $Data.Rows) {
        $Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $ServerName }
        foreach ($Column in $Data.Columns.ColumnName) {
            $Row[$Column] = $Source[$Column]
        }
        Add-InventoryRow -Connection $Connection -Table $Table -Row $Row
    }
    $Data.Rows.Count
}

function Write-CollectionLog {
    # One row per server per collection per run, so a server that failed is never just missing.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Collection,
        [Parameter(Mandatory)][string]$ServerName,
        [Parameter(Mandatory)][datetime]$CaptureDate,
        [Parameter(Mandatory)][ValidateSet('SUCCESS', 'PARTIAL', 'FAILED')][string]$Status,
        [int]$Rows = 0,
        [string]$ErrorMessage
    )

    Add-InventoryRow -Connection $Connection -Table 'dbo.CollectionLog' -Row ([ordered]@{
        CaptureDate      = $CaptureDate
        Collection       = $Collection
        ServerName       = $ServerName
        CollectionStatus = $Status
        RowsCollected    = $Rows
        ErrorMessage     = $ErrorMessage
    })
}

The tables

The script stops with a clear message if these are missing. Run this once before the first collection.

-- Run once in the DBA database on the inventory server, before the first run of
-- the script. Safe to run again: each table is only created if it's missing.
IF DB_ID(N'DBA') IS NULL
    CREATE DATABASE DBA;
GO

USE DBA;
GO

-- Shared by the collectors: one row per server per run, so a server that
-- failed is never just missing from the report
IF OBJECT_ID(N'dbo.CollectionLog', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.CollectionLog
    (
        CollectionLogID  BIGINT IDENTITY(1,1) NOT NULL
                         CONSTRAINT PK_CollectionLog PRIMARY KEY NONCLUSTERED,
        CaptureDate      DATETIME2(0)  NOT NULL,
        Collection       VARCHAR(30)   NOT NULL,   -- Integrity, WindowsPatch, SqlPatch, Security, Capacity
        ServerName       NVARCHAR(128) NOT NULL,
        CollectionStatus VARCHAR(20)   NOT NULL,   -- SUCCESS, PARTIAL, FAILED
        RowsCollected    INT           NULL,
        ErrorMessage     NVARCHAR(MAX) NULL
    );
    CREATE CLUSTERED INDEX CX_CollectionLog
        ON dbo.CollectionLog (Collection, ServerName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.SecurityServerRoleMember', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SecurityServerRoleMember
    (
        CaptureDate      DATETIME2(0)  NOT NULL,
        ServerName       NVARCHAR(128) NOT NULL,
        RoleOrPermission NVARCHAR(160) NOT NULL,   -- sysadmin, securityadmin ... or 'PERMISSION: CONTROL SERVER'
        MemberName       NVARCHAR(128) NOT NULL,
        MemberType       NVARCHAR(60)  NULL,
        IsDisabled       BIT           NULL
    );
    CREATE CLUSTERED INDEX CX_SecurityServerRoleMember
        ON dbo.SecurityServerRoleMember (ServerName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.SecurityLogin', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SecurityLogin
    (
        CaptureDate         DATETIME2(0)  NOT NULL,
        ServerName          NVARCHAR(128) NOT NULL,
        LoginName           NVARCHAR(128) NOT NULL,
        LoginType           NVARCHAR(60)  NULL,
        IsDisabled          BIT           NULL,
        IsSysadmin          BIT           NULL,
        CreateDate          DATETIME2(0)  NULL,
        ModifyDate          DATETIME2(0)  NULL,
        DefaultDatabase     NVARCHAR(128) NULL,
        PasswordLastSet     DATETIME2(0)  NULL,     -- SQL logins only
        IsPolicyChecked     BIT           NULL,
        IsExpirationChecked BIT           NULL,
        WeakPassword        NVARCHAR(40)  NULL      -- 'Password = login name' / 'Blank password'
    );
    CREATE CLUSTERED INDEX CX_SecurityLogin
        ON dbo.SecurityLogin (ServerName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.SecurityOrphanedUser', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SecurityOrphanedUser
    (
        CaptureDate       DATETIME2(0)  NOT NULL,
        ServerName        NVARCHAR(128) NOT NULL,
        DatabaseName      NVARCHAR(128) NOT NULL,
        UserName          NVARCHAR(128) NOT NULL,
        LoginWithSameName NVARCHAR(128) NULL,
        FixStatement      NVARCHAR(600) NULL
    );
    CREATE CLUSTERED INDEX CX_SecurityOrphanedUser
        ON dbo.SecurityOrphanedUser (ServerName, CaptureDate);
END;
GO

The report queries

Run these in the DBA database. The change list needs two successful runs for a server before it shows anything. @PasswordAgeDays is 365; set it to your own policy.

/*
SecurityReport.sql - run in the DBA database.
Monthly for security and audit; the change list (section 1) is worth a look
after every run. Every change should match a change ticket.
*/
USE DBA;

DECLARE @PasswordAgeDays INT = 365;

/* 1. What changed since the previous run: role members, high permissions, logins */
WITH Runs AS
(
    SELECT ServerName, CaptureDate,
           ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureDate DESC) AS RunNo
    FROM dbo.CollectionLog
    WHERE Collection = 'Security' AND CollectionStatus = 'SUCCESS'
),
CurMembers AS
(
    SELECT M.* FROM dbo.SecurityServerRoleMember AS M
    JOIN Runs AS R ON R.ServerName = M.ServerName AND R.CaptureDate = M.CaptureDate AND R.RunNo = 1
),
PrevMembers AS
(
    SELECT M.* FROM dbo.SecurityServerRoleMember AS M
    JOIN Runs AS R ON R.ServerName = M.ServerName AND R.CaptureDate = M.CaptureDate AND R.RunNo = 2
),
CurLogins AS
(
    SELECT L.* FROM dbo.SecurityLogin AS L
    JOIN Runs AS R ON R.ServerName = L.ServerName AND R.CaptureDate = L.CaptureDate AND R.RunNo = 1
),
PrevLogins AS
(
    SELECT L.* FROM dbo.SecurityLogin AS L
    JOIN Runs AS R ON R.ServerName = L.ServerName AND R.CaptureDate = L.CaptureDate AND R.RunNo = 2
),
Changes AS
(
    SELECT
        COALESCE(C.ServerName, P.ServerName)             AS ServerName,
        COALESCE(C.RoleOrPermission, P.RoleOrPermission) AS ChangeArea,
        COALESCE(C.MemberName, P.MemberName)             AS PrincipalName,
        CASE WHEN P.MemberName IS NULL THEN 'ADDED' ELSE 'REMOVED' END AS Change
    FROM CurMembers AS C
    FULL JOIN PrevMembers AS P
           ON P.ServerName = C.ServerName
          AND P.RoleOrPermission = C.RoleOrPermission
          AND P.MemberName = C.MemberName
    WHERE C.MemberName IS NULL OR P.MemberName IS NULL

    UNION ALL

    SELECT
        COALESCE(C.ServerName, P.ServerName),
        'Login',
        COALESCE(C.LoginName, P.LoginName),
        CASE WHEN P.LoginName IS NULL THEN 'ADDED'
             WHEN C.LoginName IS NULL THEN 'REMOVED'
             WHEN C.IsDisabled = 1 AND P.IsDisabled = 0 THEN 'DISABLED'
             WHEN C.IsDisabled = 0 AND P.IsDisabled = 1 THEN 'ENABLED'
             ELSE 'PASSWORD OR SETTINGS CHANGED' END
    FROM CurLogins AS C
    FULL JOIN PrevLogins AS P
           ON P.ServerName = C.ServerName
          AND P.LoginName = C.LoginName
    WHERE C.LoginName IS NULL
       OR P.LoginName IS NULL
       OR C.IsDisabled <> P.IsDisabled
       OR C.ModifyDate <> P.ModifyDate
)
SELECT CH.ServerName, CH.ChangeArea, CH.PrincipalName, CH.Change,
       P.CaptureDate AS PreviousRun, C.CaptureDate AS LatestRun
FROM Changes AS CH
JOIN Runs AS C ON C.ServerName = CH.ServerName AND C.RunNo = 1
JOIN Runs AS P ON P.ServerName = CH.ServerName AND P.RunNo = 2    -- servers with two runs only
ORDER BY CASE WHEN CH.ChangeArea = 'sysadmin' OR CH.ChangeArea LIKE 'PERMISSION:%' THEN 0 ELSE 1 END,
         CH.ServerName, CH.ChangeArea, CH.PrincipalName;

/* 2. Who has sysadmin, or equivalent, today ------------------------------- */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Security' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT M.ServerName, M.RoleOrPermission, M.MemberName, M.MemberType, M.IsDisabled
FROM dbo.SecurityServerRoleMember AS M
JOIN LatestRun AS R ON R.ServerName = M.ServerName AND R.CaptureDate = M.CaptureDate
WHERE M.RoleOrPermission IN ('sysadmin', 'securityadmin', 'PERMISSION: CONTROL SERVER',
                             'PERMISSION: IMPERSONATE ANY LOGIN', 'PERMISSION: ALTER ANY LOGIN')
ORDER BY M.ServerName, M.RoleOrPermission, M.MemberName;

/* 3. Members per role across the estate: the one-line summary for the pack */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Security' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT M.RoleOrPermission,
       COUNT(*)                     AS Memberships,
       COUNT(DISTINCT M.MemberName) AS DistinctMembers,
       COUNT(DISTINCT M.ServerName) AS Servers
FROM dbo.SecurityServerRoleMember AS M
JOIN LatestRun AS R ON R.ServerName = M.ServerName AND R.CaptureDate = M.CaptureDate
GROUP BY M.RoleOrPermission
ORDER BY CASE WHEN M.RoleOrPermission = 'sysadmin' THEN 0 ELSE 1 END, M.RoleOrPermission;

/* 4. Login problems ------------------------------------------------------- */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Security' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT
    L.ServerName,
    L.LoginName,
    L.LoginType,
    L.IsDisabled,
    L.IsSysadmin,
    L.PasswordLastSet,
    CONCAT_WS(', ',
        L.WeakPassword,
        CASE WHEN L.LoginType = 'SQL_LOGIN' AND L.IsPolicyChecked = 0           THEN 'Password policy off' END,
        CASE WHEN L.LoginType = 'SQL_LOGIN' AND L.IsDisabled = 0
              AND L.PasswordLastSet < DATEADD(DAY, -@PasswordAgeDays, L.CaptureDate) THEN 'Password older than ' + CAST(@PasswordAgeDays AS VARCHAR(5)) + ' days' END,
        CASE WHEN L.LoginName = 'sa' AND L.IsDisabled = 0                       THEN 'sa is enabled' END,
        CASE WHEN L.IsSysadmin = 1 AND L.LoginType = 'SQL_LOGIN'
              AND L.LoginName <> 'sa' AND L.IsDisabled = 0                       THEN 'SQL login with sysadmin' END
    ) AS Problems
FROM dbo.SecurityLogin AS L
JOIN LatestRun AS R ON R.ServerName = L.ServerName AND R.CaptureDate = L.CaptureDate
WHERE L.WeakPassword IS NOT NULL
   OR (L.LoginType = 'SQL_LOGIN' AND L.IsPolicyChecked = 0)
   OR (L.LoginType = 'SQL_LOGIN' AND L.IsDisabled = 0 AND L.PasswordLastSet < DATEADD(DAY, -@PasswordAgeDays, L.CaptureDate))
   OR (L.LoginName = 'sa' AND L.IsDisabled = 0)
   OR (L.IsSysadmin = 1 AND L.LoginType = 'SQL_LOGIN' AND L.LoginName <> 'sa' AND L.IsDisabled = 0)
ORDER BY CASE WHEN L.WeakPassword IS NOT NULL THEN 0 ELSE 1 END, L.ServerName, L.LoginName;

/* 5. Orphaned users, with the statement that fixes each one --------------- */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Security' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT O.ServerName, O.DatabaseName, O.UserName, O.LoginWithSameName, O.FixStatement
FROM dbo.SecurityOrphanedUser AS O
JOIN LatestRun AS R ON R.ServerName = O.ServerName AND R.CaptureDate = O.CaptureDate
ORDER BY O.ServerName, O.DatabaseName, O.UserName;

/* 6. Instances that couldn't be checked in the latest run ----------------- */
SELECT L.ServerName, L.CaptureDate, L.ErrorMessage
FROM dbo.CollectionLog AS L
WHERE L.Collection = 'Security'
  AND L.CaptureDate = (SELECT MAX(CaptureDate) FROM dbo.CollectionLog WHERE Collection = 'Security')
  AND L.CollectionStatus <> 'SUCCESS';

Watch out for

  • Permissions. Reading password hashes needs CONTROL SERVER (or sysadmin), and finding orphans needs access to each database. Without them, the weak password and orphan columns come back empty; everything else still works. That’s the one part of the kit that needs more than read access, so agree it with whoever owns security.
  • Windows groups aren’t expanded. If DOMAIN\SQL Admins is sysadmin, the report shows the group, not the twenty people in it. Group membership lives in Active Directory; xp_logininfo will list the members on a server when you need them.
  • Don’t extend the password check into a word list. Comparing hashes against common passwords on a production server is a password-cracking exercise. If you want that, get it approved as one.
  • Orphans with a matching name. The fix statement maps the user back to the login of the same name. Check it’s the right login first: a database restored from another server may have a user whose namesake here is a different person.
  • The report shows what changed, not who changed it. For that you need SQL Server Audit or a trace on login and role changes. This tells you where to look.

Where it fits

This is part of my Auditing section. Logins also feature in the monthly management pack described on the Audit page, and the SQL Server DBA Handbook covers setting up logins and roles properly in the first place.

Microsoft’s page on server-level roles lists what each fixed role can do.

The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors | Integrity checks | Windows patching | SQL Server patching | Capacity

Clive Roberts DBA