I am doing some research on how to implement auditing of schema and security changes and will use this post to track notes and comments and observations.
Links and References
SQL Server Audit Action Groups and Actions
An Introduction to SQL Server 2008 Audit
sys.fn_get_audit_file (Transact-SQL)
SQL Server auditing – how to get notified about an event
A Typical Audit
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
USE [master] GO /****** Object: Audit [Audit-Lincoln] Script Date: 11/4/2014 5:57:20 AM ******/ CREATE SERVER AUDIT [Audit-Lincoln] TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '7388543a-bf21-42af-a987-47a2377914ac' ) ALTER SERVER AUDIT [Audit-Lincoln] WITH (STATE = ON) GO USE [master] GO CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141103-211846] FOR SERVER AUDIT [Audit-Lincoln] ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (DBCC_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP) WITH (STATE = ON) GO USE [LBDBA] GO CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20141103-181800] FOR SERVER AUDIT [Audit-Lincoln] ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (DBCC_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH (STATE = ON) GO |
After the audit and audit specifications are created and enabled. You can use a number of functions to return the results and set alerts that will email you when an audit event happens. Below are the queries you can use to get the results:
File-based Audit Logging
|
1 2 |
SELECT * FROM sys.fn_get_audit_file('C:\Files\Audit-*.sqlaudit', DEFAULT, DEFAULT) |
This will show all the audit actions that are available for this edition of SQL Server:
|
1 |
SELECT * FROM sys.dm_audit_actions daa |
…which shows this result-set:
Here is a full audit sample:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
-- Server-level Audit (Enterprise and Standard Editions) USE [master] GO DROP SERVER AUDIT [Audit-20150109-153148] GO CREATE SERVER AUDIT [Audit-20150109-153148] TO FILE ( FILEPATH = N'E:\Audits\', MAXSIZE = 100 MB, -- or '0 MB' for unlimited MAX_FILES = 100, --or '2147483647' for unlimited RESERVE_DISK_SPACE = OFF -- will not let you set to ON if onlimited files or size are set ) WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, -- or 'FAIL_OPERATION' or 'SHUTDOWN' AUDIT_GUID = 'c380d487-3c51-429d-92dd-1196493e2686' ) ALTER SERVER AUDIT [Audit-20150109-153148] WITH (STATE = ON) GO -- Server-level Audit Specification (Enterprise and Standard Editions) USE [master] GO DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20150109-153241] GO CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20150109-153241] FOR SERVER AUDIT [Audit-20150109-153148] ADD (DBCC_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_STATE_CHANGE_GROUP) WITH (STATE = ON) GO -- Database-level Audit Specification (Enterprise Edition only) USE [LBDBA] GO DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150109-153742] GO CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150109-153742] FOR SERVER AUDIT [Audit-20150109-153148] ADD (DBCC_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP) WITH (STATE = ON) GO |
Views – 3052