Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit file
Step 1 - Create Server Audit:
Step 1-1: If Exists - Delete: (Optional Step)
Set NoCount On
Use Master
GO
If Exists (Select 1 from sys.server_audit_specifications where [name] = 'Audit-01')
Begin
Alter Server Audit Specification [Audit-01]
With (State = OFF) ;
Drop Server Audit Specification [Audit-01];
Alter Server Audit [Audit-01]
With (State = OFF) ;
Drop Server Audit [Audit-01];
End
Step 1-2: Clean-Up Files: (Optional Step)
Create Table #tmpdir (directoryinfo varchar(500))
Insert into #tmpdir exec master.dbo.xp_cmdshell 'dir C:\Logs\Audit\'
If exists (Select 1 from #tmpdir where directoryinfo like '%Audit%')
Begin
exec master..xp_cmdshell 'Del /Q C:\Logs\Audit\*.*';
End
Drop Table #tmpdir
Step 1-3: Create Audit - Server Level:
Create Server Audit [Audit-01]
To File
(
FilePath = N'C:\Logs\Audit\',
MaxSize = 1024MB,
Max_Rollover_Files = 5,
Reserve_Disk_Space = OFF
)
With
(
Queue_Delay = 1000,
On_Failure = Continue
)
Step 1-4: Turn Audit On:
Alter Server Audit [Audit-01] With (State = On)
GO
Step 2: Create Audit Specification - Server-Level:
USE [master]
GO
Create Server Audit Specification [Audit-01]
For Server Audit [Audit-01]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
--ADD (DATABASE_LOGOUT_GROUP),
--ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
--ADD (SUCCESSFUL_LOGIN_GROUP),
--ADD (LOGOUT_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
--ADD (SERVER_OPERATION_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO
Step 3: Select Audit Data:
USE [OPS]
GO
Create View [dbo].[v_AuditData]
As
Select *
From sys.fn_get_audit_file ('C:\Logs\Audit\Audit*',default,default);
GO
SELECT
[server_instance_name],
DateAdd(HOUR,-4,[event_time]) as 'EventTime', [session_id], [object_id], action_id,
[session_server_principal_name], [statement],
[server_principal_name],
[database_principal_name], [database_name],
[object_name], [file_name], Convert(XML, additional_information) as 'AdditionalInformation'
FROM sys.fn_get_audit_file ('C:\Logs\Audit\*', null, null)
Order By EventTime desc
GO
Resources:
Select * from [OPS].[dbo].[v_AuditData]
SELECT spt.[name], spt.[number], *
FROM [master].[dbo].[spt_values] spt
WHERE spt.[type] = N'EOD'
ORDER BY spt.[name];
SELECT
[server_instance_name],
DateAdd(HOUR,-4,[event_time]) as 'EventTime', [session_id], [object_id], action_id,
[session_server_principal_name], [statement],
[server_principal_name],
[database_principal_name], [database_name],
[object_name], [file_name], Convert(XML, additional_information) as 'AdditionalInformation'
FROM sys.fn_get_audit_file ('C:\Logs\Audit\*', null, null)
Order By EventTime desc
GO
Select * from sys.server_audit_specifications
Select * from sys.server_audit_specification_details order by audit_action_id asc --audit_action_id, audit_action_name
Select * from sys.database_audit_specifications
Select * from sys.database_audit_specification_details
|
|
|
|
|
|