DBA Scripts: Get Audit Specifications, DDL Triggers, and Proxy Credentials

Three Kinds of Infrastructure Most Servers Never Had in the First Place

A schema change or a new login fails on an inherited server and nothing in your own code explains it, an auditor asks what this instance records, or a SQL Agent job step reaches places its owner cannot. These three scripts list what is actually configured: Get-AuditSpecifications for SQL Server Audit and the action groups nothing covers, Get-DdlTriggers for server-level triggers that fire on DDL, and Get-ProxyAndCredentials for the Agent proxies and stored credentials a job step can run under.

Why Audit Specifications, DDL Triggers, and Proxy Credentials Matter

• Login monitoring and sp_configure checks are not a substitute for SQL Server Audit, and an instance with no server audit configured is a compliance gap on any server subject to SOX, GDPR, or PCI-DSS
• A DDL trigger that ROLLBACKs on certain schema changes can silently block a deployment, and an incoming DBA has no way to know it’s there without specifically checking
• A SQL Agent proxy lets a job step run under a different, often more privileged, Windows account than the login that owns the job, a real privilege escalation path if proxy-to-login mappings aren’t reviewed
• All three are the kind of infrastructure that either doesn’t exist yet (Audit) or exists silently and gets forgotten (DDL triggers, proxies), not the kind that shows up in a routine health check

When to Run These Scripts

• Get-AuditSpecifications, any compliance review, or when establishing what auditing exists (often nothing) on an inherited server
• Get-DdlTriggers, before any planned schema change, and when reviewing an unfamiliar server for hidden dependencies
• Get-ProxyAndCredentials, security reviews focused on SQL Agent, and whenever investigating how a job step’s permissions differ from its owning login
• Together, as a “hidden infrastructure” pass alongside the rest of the security series

The Scripts

1. Get-AuditSpecifications: SQL Server Audit Objects and Compliance Gaps

/*
Script Name : Get-AuditSpecifications
Category    : security
Purpose     : SQL Server Audit objects and specifications with compliance gap analysis.
              SQL Audit (the formal mechanism for SOX, GDPR, PCI-DSS) is completely
              separate from login monitoring. Most inherited servers have none configured.
              Surfaces missing critical action groups (FAILED_LOGIN_GROUP, privilege changes)
              and database-level audit specifications across all user databases.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-audit-triggers-and-proxy-credentials/)
Requires    : VIEW ANY DATABASE, VIEW ANY DEFINITION, CONTROL SERVER
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

CREATE TABLE #audit_info (
    result_type NVARCHAR(30),
    audit_name NVARCHAR(128),
    specification_name NVARCHAR(128),
    database_name NVARCHAR(128), -- NVARCHAR not SYSNAME; SYSNAME is NOT NULL
    action_group NVARCHAR(256),
    audit_type NVARCHAR(60),
    audit_state NVARCHAR(20),
    on_failure NVARCHAR(30),
    status NVARCHAR(400)
);

-- Server-level audit objects
INSERT INTO #audit_info
SELECT
    'SERVER_AUDIT' AS result_type,
    a.name AS audit_name,
    NULL AS specification_name,
    NULL AS database_name,
    NULL AS action_group,
    a.type_desc AS audit_type,
    CASE a.is_state_enabled WHEN 1 THEN 'STARTED' ELSE 'STOPPED' END AS audit_state,
    a.on_failure_desc AS on_failure,
    CASE
        WHEN a.is_state_enabled = 0
            THEN 'WARN - audit exists but is not running'
        WHEN a.on_failure_desc = 'CONTINUE'
            THEN 'INFO - on_failure = CONTINUE; audit records can be lost silently on I/O error'
        ELSE 'OK'
    END AS status
FROM sys.server_audits AS a;

-- Server audit specification detail
INSERT INTO #audit_info
SELECT
    'SERVER_SPEC' AS result_type,
    a.name AS audit_name,
    s.name AS specification_name,
    NULL,
    d.audit_action_name AS action_group,
    NULL,
    CASE s.is_state_enabled WHEN 1 THEN 'ENABLED' ELSE 'DISABLED' END,
    NULL,
    CASE WHEN s.is_state_enabled = 0
         THEN 'WARN - specification is disabled'
         ELSE 'OK'
    END
FROM sys.server_audit_specifications AS s
JOIN sys.server_audits AS a ON a.audit_guid = s.audit_guid
JOIN sys.server_audit_specification_details AS d ON d.server_specification_id = s.server_specification_id;

-- Gap analysis: critical server-level action groups
DECLARE @covered_groups TABLE (action_group NVARCHAR(256));
INSERT INTO @covered_groups
SELECT DISTINCT d.audit_action_name
FROM sys.server_audit_specification_details AS d
JOIN sys.server_audit_specifications AS s ON s.server_specification_id = d.server_specification_id
WHERE s.is_state_enabled = 1;

INSERT INTO #audit_info (result_type, action_group, status)
SELECT
    'GAP_CHECK',
    critical_group,
    CASE WHEN EXISTS (SELECT 1 FROM @covered_groups WHERE action_group = critical_group)
         THEN 'OK - covered by an enabled specification'
         ELSE gap_severity + ' - ' + critical_group + ' is not audited; ' + why_it_matters
    END
FROM (VALUES
    ('FAILED_LOGIN_GROUP', 'CRITICAL', 'brute-force attacks and failed access go undetected'),
    ('SERVER_ROLE_MEMBER_CHANGE_GROUP', 'CRITICAL', 'privilege escalation (adding sysadmin) is not recorded'),
    ('DATABASE_ROLE_MEMBER_CHANGE_GROUP', 'HIGH', 'db_owner grants and role membership changes unrecorded'),
    ('SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP','HIGH', 'GRANT/REVOKE/DENY on objects not captured'),
    ('SUCCESSFUL_LOGIN_GROUP', 'MEDIUM', 'no record of who connected and when'),
    ('SERVER_OBJECT_CHANGE_GROUP', 'MEDIUM', 'CREATE/ALTER/DROP SERVER OBJECT events not captured')
) AS gaps(critical_group, gap_severity, why_it_matters);

-- Database-level audit specifications (cross-database)
DECLARE @db SYSNAME;
DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
    INSERT INTO #audit_info
    SELECT
        ''DB_SPEC'',
        a.name,
        s.name,
        N' + QUOTENAME(@db, N'''') + N',
        d.audit_action_name,
        NULL,
        CASE s.is_state_enabled WHEN 1 THEN ''ENABLED'' ELSE ''DISABLED'' END,
        NULL,
        CASE WHEN s.is_state_enabled = 0 THEN ''WARN - specification disabled''
             ELSE ''OK'' END
    FROM ' + QUOTENAME(@db) + N'.sys.database_audit_specifications s
    JOIN sys.server_audits a
        ON a.audit_guid = s.audit_guid
    JOIN ' + QUOTENAME(@db) + N'.sys.database_audit_specification_details d
        ON d.database_specification_id = s.database_specification_id;';

    BEGIN TRY EXEC sp_executesql @sql; END TRY
    BEGIN CATCH END CATCH;

    FETCH NEXT FROM db_cursor INTO @db;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

IF NOT EXISTS (SELECT 1 FROM sys.server_audits)
BEGIN
    INSERT INTO #audit_info (result_type, status)
    VALUES ('GAP_CHECK',
            'CRITICAL - No SQL Server Audit objects configured on this instance. ' +
            'Login monitoring and sp_configure checks are not a substitute for SQL Audit. ' +
            'Create a server audit with FAILED_LOGIN_GROUP and SERVER_ROLE_MEMBER_CHANGE_GROUP at minimum.');
END;

SELECT
    result_type, audit_name, specification_name, database_name,
    action_group, audit_type, audit_state, on_failure, status
FROM #audit_info
ORDER BY
    CASE result_type WHEN 'GAP_CHECK' THEN 1
                     WHEN 'SERVER_AUDIT' THEN 2
                     WHEN 'SERVER_SPEC' THEN 3
                     WHEN 'DB_SPEC' THEN 4
                     ELSE 5 END,
    CASE WHEN status LIKE 'CRITICAL%' THEN 1
         WHEN status LIKE 'HIGH%' THEN 2
         WHEN status LIKE 'WARN%' THEN 3
         ELSE 4 END,
    audit_name, database_name, action_group;

DROP TABLE #audit_info;

2. Get-DdlTriggers: Server-Level Schema Change Triggers

/*
Script Name : Get-DdlTriggers
Category    : security
Purpose     : Server-level DDL triggers. These fire on schema changes (CREATE/ALTER/DROP)
              and are often unknown to incoming DBAs. Can block DDL, audit changes, or
              enforce naming conventions — a hidden dependency on inherited servers.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-audit-triggers-and-proxy-credentials/)
Requires    : VIEW ANY DEFINITION
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

SELECT
    t.name AS trigger_name,
    t.type_desc,
    t.is_disabled,
    t.create_date,
    t.modify_date,
    STUFF((
        SELECT ', ' + e.type_desc
        FROM sys.server_trigger_events AS e
        WHERE e.object_id = t.object_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(500)'), 1, 2, '') AS event_types,
    sp.name AS execute_as_principal,
    m.definition AS trigger_definition,
    CASE
        WHEN t.is_disabled = 1
            THEN 'INFO - trigger is disabled'
        WHEN m.definition LIKE '%ROLLBACK%'
            THEN 'WARN - trigger may ROLLBACK transactions; could block DDL operations'
        ELSE 'OK - review to confirm purpose and owner'
    END AS status
FROM sys.server_triggers AS t
JOIN sys.server_sql_modules AS m ON m.object_id = t.object_id
LEFT JOIN sys.server_principals AS sp ON sp.principal_id = m.execute_as_principal_id
ORDER BY t.name;

3. Get-ProxyAndCredentials: SQL Agent Proxies and Server Credentials

/*
Script Name : Get-ProxyAndCredentials
Category    : security
Purpose     : Lists SQL Agent proxies and server-level credentials with their identity
              and associated subsystems. Proxies that use stored credentials to run Agent
              steps under a different account are a common privilege escalation path.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-audit-triggers-and-proxy-credentials/)
Requires    : VIEW SERVER STATE, db_datareader on msdb (or sysadmin); the Agent roles alone cannot SELECT sysproxylogin
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  DESIGN: Two row sources unified via UNION ALL:
    1. SQL Agent proxies (msdb.dbo.sysproxies) — run steps under an alternate Windows account
    2. Server-level credentials (sys.credentials) — used by proxies, linked servers, and BACKUP
  The subsystem list for each proxy is aggregated from msdb.dbo.sysproxysubsystem.
  Credential identity is the Windows account or certificate the credential maps to.
*/

-- SQL Agent proxies
SELECT
    'Proxy' AS type,
    p.name AS name,
    p.enabled AS is_enabled,
    c.name AS credential_name,
    c.credential_identity AS runs_as,
    (
        SELECT STRING_AGG(ss.subsystem, ', ')
        FROM msdb.dbo.sysproxysubsystem ps
        JOIN msdb.dbo.syssubsystems ss ON ss.subsystem_id = ps.subsystem_id
        WHERE ps.proxy_id = p.proxy_id
    ) AS allowed_subsystems,
    (
        SELECT STRING_AGG(l.name, ', ')
        FROM msdb.dbo.sysproxylogin pl
        JOIN sys.server_principals l ON l.sid = pl.sid
        WHERE pl.proxy_id = p.proxy_id
    ) AS allowed_logins,
    p.description
FROM msdb.dbo.sysproxies p
LEFT JOIN sys.credentials c ON c.credential_id = p.credential_id

UNION ALL

-- Server-level credentials not used by any proxy (standalone)
SELECT
    'Credential' AS type,
    c.name AS name,
    1 AS is_enabled,
    c.name AS credential_name,
    c.credential_identity AS runs_as,
    NULL AS allowed_subsystems,
    NULL AS allowed_logins,
    NULL AS description
FROM sys.credentials c
WHERE NOT EXISTS (
    SELECT 1 FROM msdb.dbo.sysproxies p WHERE p.credential_id = c.credential_id
)

ORDER BY type, name;

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# SQL Server Audit objects and compliance gap analysis:
.\run.ps1 Get-AuditSpecifications

# Server-level DDL triggers:
.\run.ps1 Get-DdlTriggers

# SQL Agent proxies and server credentials:
.\run.ps1 Get-ProxyAndCredentials

# Any of these against a remote sql server:
.\run.ps1 Get-AuditSpecifications -ServerInstance SQLSERVER01

These scripts live in the repo at:

• sql/security/Get-AuditSpecifications.sql
• sql/security/Get-DdlTriggers.sql
• sql/security/Get-ProxyAndCredentials.sql

Example Output

One run on the lab instance, with 1 server audit writing to a file, 1 server audit specification and 1 database audit specification in Lab1005_aud_db, returned 11 rows from Get-AuditSpecifications. audit_name, specification_name, audit_type and on_failure are left out for width.

The 2 CRITICAL action groups read OK because the server specification lists them. The 2 HIGH rows still say not audited, although the DB_SPEC rows show both groups audited in Lab1005_aud_db: the gap check reads server audit specifications only, so a database specification never clears a GAP_CHECK row.

Get-DdlTriggers on the same instance returned 1 row, the server trigger Lab1005_aud_trg with event_types of CREATE_LOGIN and a status of OK - review to confirm purpose and owner. Disabled, the same row reads INFO - trigger is disabled.

Get-ProxyAndCredentials returned 2 rows: the proxy Lab1005_aud_proxy, running as NT AUTHORITY\LOCAL SERVICE for CmdExec with 1 allowed login, and the credential Lab1005_aud_cred2 as a Credential row because no proxy uses it.

Understanding the Results

Get-AuditSpecifications

Get-DdlTriggers

Get-ProxyAndCredentials

Best Practices

• Treat a completely unconfigured SQL Server Audit as a real compliance gap on any server subject to SOX, GDPR, or PCI-DSS, not just a nice-to-have
• At minimum, create a server audit covering FAILED_LOGIN_GROUP and SERVER_ROLE_MEMBER_CHANGE_GROUP, the two CRITICAL gaps this script flags by default
• Document every DDL trigger’s purpose and owner when found, an undocumented trigger that blocks DDL is a recurring source of confusing deployment failures
• Review every proxy-to-credential mapping specifically for privilege escalation, a proxy running under a more privileged account than its calling login needs a documented reason

Related Scripts

You may also find these scripts useful:

• Security (hub)
• Permissions and Role Membership
• Login Security Audit
• SQL Agent and Jobs (hub)
• SQL Agent Job Failed: Finding the Step That Actually Broke, when a job step fails under a proxy account
• Schema Change History, the DDL that already ran, from the default trace
• Fix “Msg 207: Invalid Column Name”
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

Some of the most important security infrastructure on a SQL Server instance is invisible until you specifically go looking: formal auditing, DDL triggers guarding schema changes, and proxy accounts that quietly grant elevated privilege to job steps. These three scripts turn “does this exist” into a direct answer, and on a server that’s never had any of it configured, that answer is itself the finding.

Run all three during any security or compliance review, and treat a completely unconfigured SQL Server Audit as a real gap to close, not just a line item to note.