--Create Audit
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO
--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)
--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)
--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)
2 comments:
--SAMPLE COLLECTION
select top 100 *
from sys.fn_get_audit_file('e:\Audit\DDL-Audit_A1024EDC-539B-48EA-A31C-1BB307415F39_0_129606570581250000.sqlaudit', DEFAULT, DEFAULT)
select * from sys.dm_audit_actions order by action_id
select * from sys.dm_audit_class_type_map order by class_type
select * from sys.server_audits
select * from sys.server_file_audits
select * from sys.dm_server_audit_status
select * from sys.server_audit_specifications
select * from sys.server_audit_specification_details
Hi friends,
Some actions performed on SQL Server auditing components are intrinsically audited in a specific audit, and in these cases audit events occur automatically because the event occurred on the parent object. Thanks a lot....
Public Folders
Post a Comment