Share via

SQL Server 2008 R2 - User Action Audit Sample

  • Article

-- Create Server Audit
USE master
GO

-- Create Server Audit on File
CREATE SERVER AUDIT AdventureWorks_Audit
    TO FILE ( FILEPATH ='\\myComputer\shares' , MAXSIZE=2MB, MAX_ROLLOVER_FILES=10 )
GO

ALTER SERVER AUDIT AdventureWorks_Audit
WITH (STATE = ON) ;
GO

-- Move to the target database
USE AdventureWorks
GO

-- Create the database audit specification
CREATE DATABASE AUDIT SPECIFICATION AllActionAudit_Pay_Table
FOR SERVER AUDIT AdventureWorks_Audit
ADD (SELECT, INSERT
     ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON)
GO

-- Create some actions and verify the audit log file
SELECT * FROM HumanResources.EmployeePayHistory
GO

INSERT INTO HumanResources.EmployeePayHistory
VALUES (291, '2009-09-01', 50, 2, GETDATE())
Go

SELECT * FROM HumanResources.EmployeePayHistory
WHERE EmployeeID = 291
GO

 

REFERENCE: