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