WebsiteSpark

Monday, 21 July 2014

TSQL SQL Server Auditing setup

/*STEP1 Create server Audit specification*/


USE [master]
GO

CREATE SERVER AUDIT [TestAudit]
TO FILE 
( FILEPATH = N'D:\temp\' /*Specify the folder path here*/
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO


/*STEP 2 Create Database Audit specification*/

USE [MyDB]
GO

CREATE DATABASE AUDIT SPECIFICATION [TestDBAudit] 
FOR SERVER AUDIT [TestAudit]
ADD (DELETE ON OBJECT::[dbo].[MyTbl] BY [db_owner]),  /* Object name is the table name */
ADD (UPDATE ON OBJECT::[dbo].[MyTbl] BY [db_owner]),   /* Object name is the table name */
ADD (INSERT ON OBJECT::[dbo].[MyTbl] BY [db_owner])   /* Object name is the table name */
WITH (STATE = OFF)
GO


/*STEP 3 Enable database Audit*/

USE [MyDB]
GO
Alter DATABASE AUDIT SPECIFICATION [TestDBAudit]
WITH (STATE = ON)
GO


/*STEP 4 Enable Server Audit*/

USE [master]
GO
Alter SERVER AUDIT [TestAudit]
With (STATE = ON)
GO


/*STEP 5 View the audit file */

SELECT * FROM sys.fn_get_audit_file 
(N'D:\temp\<FileName>.sqlaudit',
default,default);
GO

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This is really good command for auditing sql server but I tried automated sql server auditing solution which helps to audit particular user activities and operations as per requirement. It provides to facilitates to monitor the SQL server changes as who, what , when made and where.

    ReplyDelete