
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_logininfowill 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
2 thoughts on “SQL Server Security Report: Who Has sysadmin, and What Changed”
Comments are closed.