#MSSQL Auditing in Sql Server

sql_audit.md

SQL Server Audit

1. Create Audit Object

1. Navigate to [Server] > Security > Audits
2. New Audit
3. Set "Audit name"
4. Set "Queue delay". (Increase delay between log writes for busy servers)
5. Set "On Audit Log Failure" to "Continue"
6. Set "Audit destination" to "Security Log" or "File". "Security Log" stores in the Window's Event Viewer. * Choose "Maximum rollover files" to specify that when the maximum number of files is reached, the oldest audit files are overwritten. * Specify maximum number of files that can be created and the maximum size of each file. "Unlimited" is an available option, but is not recommended.

2. Create Database Audit Specification

1. Navigate to [Server] > Databases > [Database] > Security > Database Audit Specifications
2. New Database Audit Specification
3. Set "Name"
4. Choose audit object in "Audit" dropdown
5. Set "Actions": * Set "Audit Action Type": select, insert, etc. * Set "Object Class": "Object" for table. * Set "Object Name": browse for table names * Set "Principal Name": browse for users or roles

If GUI fails, create using this script or similar:

USE master ;
GO
-- Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
    TO FILE ( FILEPATH = 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA' ) ;
GO
-- Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit 
WITH (STATE = ON) ;
GO
-- Move to the target database.
USE AdventureWorks2008R2 ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
     ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON) ;
GO

Reference: MSDN audit

Example 2:

USE [master]
GO

CREATE SERVER AUDIT [Audit-20150923-215753-MyTable]
TO FILE 
(	FILEPATH = N'B:\audit_log'
	,MAXSIZE = 5 MB
	,MAX_ROLLOVER_FILES = 10000
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 5000
	,ON_FAILURE = CONTINUE
)
GO

USE [MyDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150923-221230-MyTable]
FOR SERVER AUDIT [Audit-20150923-215753-MyTable]
ADD (INSERT ON OBJECT::[dbo].[MyTable] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[MyTable] BY [public]),
ADD (DELETE ON OBJECT::[dbo].[MyTable] BY [public])

GO

3. Enable Auditing

1. Navigate to [Server] > Security > Audits
2. Right click Audit object and select "Enable Audit"

4. View Audit Log

1. Navigate to [Server] > Security > Audits
2. Right click Audit object and select "View Audit Logs"

Note: If you cannot load the audit file from your location, you can run the following:

-- Reads from a file that is named \\serverName\Audit\HIPPA_AUDIT.sqlaudit 
SELECT * FROM sys.fn_get_audit_file ('\\serverName\Audit\HIPPA_AUDIT.sqlaudit',default,default);
GO

Or

-- Collect all audit files in the specified location.
SELECT * FROM sys.fn_get_audit_file ('F:\Audit\*',default,default);
GO

Or

IF OBJECT_ID('tempdb..#TempAudit42') IS NOT NULL DROP TABLE #TempAudit42
SELECT * INTO #TempAudit42 FROM sys.fn_get_audit_file ('G:\AUDIT_LOGS\*',default,default);
SELECT DISTINCT
CAST(TA.event_time AS DATETIME2(0)) as [event_time], TA.session_id, AA.name AS action_name, TA.database_name, TA.object_name, TA.succeeded, TA.session_server_principal_name, TA.server_principal_name, REPLACE(REPLACE(TA.statement, CHAR(13), ''), CHAR(10), '') AS [statement]
FROM #TempAudit42 TA
JOIN sys.dm_audit_actions AA ON TA.action_id = AA.action_id
WHERE
(file_name LIKE 'G:\AUDIT_LOGS\Audit-20160218-102436-ServerPermission%'
OR
file_name LIKE 'G:\AUDIT_LOGS\Audit-20160218-102647-DatabasePermission%')
ORDER BY [event_time] DESC

Reference: MSDN log

5. View Audit Status

SELECT 
    a.name as 'server_audit_name', 
    s.name as 'database_audit_name', 
    s.is_state_enabled, object_name(d.major_id) as 'object_name', 
    audit_action_name
FROM sys.server_audits AS a
    LEFT JOIN sys.database_audit_specifications AS s
	   ON a.audit_guid = s.audit_guid
    LEFT JOIN sys.database_audit_specification_details AS d
	   ON s.database_specification_id = d.database_specification_id

Reference: MSDN audit status