sql server audit

use master
— Create the server audit.
create SERVER AUDIT auditname
TO application_LOG
with
(ON_FAILURE = CONTINUE,
QUEUE_DELAY = 1000,
AUDIT_GUID = ‘e9f52225-4e78-48f2-b3f4-8e1de833d7bf’)
GO

— Move to the target database.
USE userdb ;
GO
— Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION DBSpecificationName
FOR SERVER AUDIT auditname
ADD (Update ON OBJECT::[dbo].[table1] BY [dbo]),
ADD (delete ON OBJECT::[dbo].[table1] BY [dbo]),
ADD (Update ON OBJECT::[dbo].[table2] BY [dbo]),
ADD (delete ON OBJECT::[dbo].[table2] BY [dbo])
WITH (STATE = OFF)
GO

use master
— Enable the server audit.
ALTER SERVER AUDIT auditname
WITH (STATE = on) ;
GO

use userdb
–enable audit spec
alter database audit specification DBSpecificationName
with (state = on)

 

note: to use security_log GPO needs to be modified
https://technet.microsoft.com/en-us/library/cc645889(v=sql.110).aspx

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.