info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



SQL Server Audit
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-05-20








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