{"id":1426,"date":"2026-05-01T19:27:16","date_gmt":"2026-05-01T19:27:16","guid":{"rendered":"https:\/\/infinitechno.com\/techno\/?p=1426"},"modified":"2026-05-01T19:29:24","modified_gmt":"2026-05-01T19:29:24","slug":"steps-to-enable-sql-database-auditing-effectively","status":"publish","type":"post","link":"https:\/\/infinitechno.com\/techno\/2026\/05\/01\/steps-to-enable-sql-database-auditing-effectively\/","title":{"rendered":"Steps to Enable SQL Database Auditing Effectively"},"content":{"rendered":"\n\n\n\n
\n
\n
\"\"<\/figure>\n\n\n\n
\n
<\/summary><\/details>\n<\/div><\/div>\n<\/div>\n<\/div>\n\n

Step 1: Create an Audit Object<\/strong><\/strong><\/p>\n\n

<\/p>\n\n

Download PDF: Configure SQL Server Database Audit<\/a><\/p>\n\n

USE master;
GO

CREATE SERVER AUDIT Audit_SQL_Tracking
TO FILE
(
    FILEPATH = ‘C:\\SQLAudit\\’,   — make sure folder exists
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10
)
WITH (ON_FAILURE = CONTINUE);
GO<\/p>\n\n

\n\n

Step 2: Enable the Audit<\/strong><\/strong><\/p>\n\n

ALTER SERVER AUDIT Audit_SQL_Tracking
WITH (STATE = ON);
GO<\/p>\n\n

\n\n

\u2705<\/strong> 2. Create Database-Level Audit Specification<\/strong><\/strong><\/p>\n\n

Now we define what actions to track inside a specific database<\/strong>.<\/p>\n\n

USE YourDatabaseName;
GO

CREATE DATABASE AUDIT SPECIFICATION Audit_DB_Tracking
FOR SERVER AUDIT Audit_SQL_Tracking<\/p>\n\n

\n\n

\u2705<\/strong> 3. Add Audit Actions (Your Requirements)<\/strong><\/strong><\/p>\n\n

You asked to track:<\/p>\n\n

    \n
  • CREATE TABLE<\/li>\n\n\n\n
  • DROP TABLE<\/li>\n\n\n\n
  • DELETE<\/li>\n\n\n\n
  • CREATE PROCEDURE<\/li>\n\n\n\n
  • DROP PROCEDURE<\/li>\n<\/ul>\n\n

    Here\u2019s the full script:<\/p>\n\n

    ALTER DATABASE AUDIT SPECIFICATION Audit_DB_Tracking
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),   — CREATE \/ ALTER \/ DROP tables, procedures, views
    ADD (DELETE ON DATABASE::YourDatabaseName BY PUBLIC),  — DELETE operations
    ADD (SCHEMA_OBJECT_ACCESS_GROUP);  — Access tracking (optional but useful)
    GO<\/p>\n\n

    \n\n

    What Each Option Means<\/strong><\/p>\n\n

    Option<\/strong><\/strong><\/td>Tracks<\/strong><\/strong><\/td><\/tr>
    SCHEMA_OBJECT_CHANGE_GROUP<\/td>CREATE, ALTER, DROP (tables, procedures, etc.)<\/td><\/tr>
    DELETE<\/td>Tracks DELETE statements<\/td><\/tr>
    SCHEMA_OBJECT_ACCESS_GROUP<\/td>SELECT, EXECUTE (optional)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n
    \n\n

    \u2705<\/strong> 4. Enable Database Audit<\/strong><\/strong><\/p>\n\n

    ALTER DATABASE AUDIT SPECIFICATION Audit_DB_Tracking
    WITH (STATE = ON);
    GO<\/p>\n\n

    \n\n

    \u2705<\/strong> 5. View Audit Logs<\/strong><\/strong><\/p>\n\n

    You can read audit logs using:<\/p>\n\n

    SELECT *
    FROM sys.fn_get_audit_file (
        ‘C:\\SQLAudit\\*.sqlaudit’,
        DEFAULT,
        DEFAULT
    );<\/p>\n\n

    \n\n

    \u2705<\/strong> 6. Filter Specific Events (Example)<\/strong><\/strong><\/p>\n\n

    Only show DELETE operations:<\/p>\n\n

    SELECT event_time, action_id, statement
    FROM sys.fn_get_audit_file (
        ‘C:\\SQLAudit\\*.sqlaudit’,
        DEFAULT,
        DEFAULT
    )
    WHERE action_id = ‘DL’;  — DELETE<\/p>\n\n

    \n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":1428,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"pagelayer_contact_templates":[],"_pagelayer_content":"","_glsr_average":0,"_glsr_ranking":0,"_glsr_reviews":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[49,91],"tags":[],"class_list":["post-1426","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql","category-sql-server-best-practices"],"acf":[],"jetpack_featured_media_url":"https:\/\/infinitechno.com\/techno\/wp-content\/uploads\/2026\/05\/Im1JF4r8lZ21777663013.jpeg","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/posts\/1426","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/comments?post=1426"}],"version-history":[{"count":3,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/posts\/1426\/revisions"}],"predecessor-version":[{"id":1431,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/posts\/1426\/revisions\/1431"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/media\/1428"}],"wp:attachment":[{"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/media?parent=1426"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/categories?post=1426"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/infinitechno.com\/techno\/wp-json\/wp\/v2\/tags?post=1426"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}