regarding SQL Server Audit RRS feed

  • Question

  • Hello,

              I want to know , If I disable or enable SQL Server Audits, where I can find this log of enable/disable of SQL Server Audits. (date/time and login user name who has toggled the SQL Server Audits)




    Saturday, March 9, 2013 4:56 PM


  • Hi Anoop,

    To view Audit Logs , goto Object Explorer -> Security - >  Audits - > right-click on your Audit - >  View Audit Logs


    SELECT SA.audit_id AuditId,
    SA.name AuditName,
    SA.create_date CreatedDate,
    SAS.status_time LastModifiedDate,
    SAS.status_desc CurrentAuditStatus,
    SP.name ServerPrincipalName
    FROM sys.dm_server_audit_status SAS
    JOIN sys.server_audits SA ON SA.audit_id = SAS.audit_id
    JOIN sys.server_principals SP ON SP.principal_id = SA.principal_id
    --SQL Server Audit related catalog views
    SELECT * FROM sys.database_audit_specifications
    SELECT * FROM sys.database_audit_specification_details
    SELECT * FROM sys.server_audit_specifications
    SELECT * FROM sys.server_audit_specification_details
    SELECT * FROM sys.server_file_audits
    SELECT * FROM sys.dm_server_audit_status
    SELECT * FROM sys.server_audits

    Thanks & Regards, sathya

    • Proposed as answer by Uri DimantMVP Sunday, March 10, 2013 10:48 AM
    • Marked as answer by Maggie Luo Tuesday, March 19, 2013 10:14 AM
    Sunday, March 10, 2013 6:00 AM