Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
83 lines (73 sloc) 2.33 KB

In order to setup MS SQL audit trail, the following should be executed:

USE [master]
GO

CREATE SERVER AUDIT [Audit-LogSentinel]
TO FILE 
(   FILEPATH = N'C:\auditlogs\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '69691cea-886a-4740-8e17-c5654e75aea5'
)
ALTER SERVER AUDIT [Audit-LogSentinel] WITH (STATE = ON)
GO

USE [database_name]


CREATE DATABASE AUDIT SPECIFICATION LogSentinelAuditSpec
FOR SERVER AUDIT [Audit-LogSentinel]
     ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES 
ON DATABASE::database_name BY PUBLIC);

ALTER DATABASE AUDIT SPECIFICATION [LogSentinelAuditSpec] WITH (STATE = ON);

If per-server audit specifications are needed, the following should be executed

CREATE SERVER AUDIT SPECIFICATION LogSentinelAuditServerSpec
FOR SERVER AUDIT [Audit-LogSentinel]
ADD(APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD(AUDIT_CHANGE_GROUP),
ADD(BACKUP_RESTORE_GROUP),
ADD(BROKER_LOGIN_GROUP),
ADD(DATABASE_CHANGE_GROUP),
ADD(DATABASE_LOGOUT_GROUP),
ADD(DATABASE_MIRRORING_LOGIN_GROUP),
ADD(DATABASE_OBJECT_ACCESS_GROUP),
ADD(DATABASE_OBJECT_CHANGE_GROUP),
ADD(DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD(DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD(DATABASE_OPERATION_GROUP),
ADD(DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD(DATABASE_PERMISSION_CHANGE_GROUP),
ADD(DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD(DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD(DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD(DBCC_GROUP),
ADD(FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD(FAILED_LOGIN_GROUP),
ADD(FULLTEXT_GROUP),
ADD(LOGIN_CHANGE_PASSWORD_GROUP),
ADD(LOGOUT_GROUP),
ADD(SCHEMA_OBJECT_ACCESS_GROUP),
ADD(SCHEMA_OBJECT_CHANGE_GROUP),
ADD(SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD(SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD(SERVER_OBJECT_CHANGE_GROUP),
ADD(SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD(SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD(SERVER_OPERATION_GROUP),
ADD(SERVER_PERMISSION_CHANGE_GROUP),
ADD(SERVER_PRINCIPAL_CHANGE_GROUP),
ADD(SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD(SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD(SERVER_STATE_CHANGE_GROUP),
ADD(SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD(SUCCESSFUL_LOGIN_GROUP),
ADD(TRACE_CHANGE_GROUP),
ADD(TRANSACTION_GROUP),
ADD(USER_CHANGE_PASSWORD_GROUP),
ADD(USER_DEFINED_AUDIT_GROUP);

GO