DATABASE AUDIT SPECIFICATION

Hi All,

I had implemented the DATABASE AUDIT SPECIFICATION to capture the DML (insert, update & delete)audit logs for principle name = db_datawriter group and its applied one by one databases on servers. But I want to enable DML logs capture on selected multiple databases, than how should I enable the settings. So I will bypass the one by one database audit enable activity.

In short I want to enable DML logs  capture on selected multiple databases with single DATABASE AUDIT SPECIFICATION. If its possible, Please suggest the steps or link to achieve the same.

Thanks

Subhash Chander

July 8th, 2015 8:54am

Hello,

a database audit is a database level object, you have to create it in each database, it is not possible to create it once on server Level.

See CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) => Remarks

Free Windows Admin Tool Kit Click here and download it now
July 8th, 2015 9:31am

 you should be able to script the database audit and create them in multiple database.

you can have multiple database audit write to the same audit file, so, you would just need to create the database audit in each user database you need.

 

July 8th, 2015 11:10am

Hi Stan210,

Can you please help me for this script or any link which can give me clarity to process for the same.

Free Windows Admin Tool Kit Click here and download it now
July 9th, 2015 1:03am

Can you please help me for this script or any link which can give me clarity to process for the same.

So you have done this in one database? Now you just go ahead do the same thing in other databases.

If you set up the first specification by point-and-click, you will need to do the point-and-click once more, but this time do not press OK. Instead press the Script bottom that is on the top of most UIs in SSMS to give you a script which you then can use for all databases you want to set up the specification for.

July 9th, 2015 4:27am

Hi Subhash,

In addition to other post, you can right click the existing  database audit specification, select Script Database Audit Specification as and then CREATE TO to generate the script. Then execute the scripts like the following example to create database audit on other databases.

EXEC sp_MSForeachdb ' if  ''?'' IN(''DatabaseName1'', '' DatabaseName2'') BEGIN 

USE [?] 

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditName]

FOR SERVER AUDIT [ServerAuditName]

ADD (UPDATE ON DATABASE::[?] BY [db_datawriter])

WITH (STATE = ON)

END'


Thanks,
Lydia Zhang

 

Free Windows Admin Tool Kit Click here and download it now
July 9th, 2015 4:37am

Hi All,

Thanks for your valuable inputs, Its helpful for me. 

Thanks

Subahsh

July 10th, 2015 1:34am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics