well I hope it works for you, I personally like to keep the audit data in separate files, the reason being is (1) I can manage audit myself, what ever (purge) policy is defined for event/security/application log will not erase my data otherwise I will have to run after sysadmins to have it restored. (2) it is kind of filter in groups. so it makes it easy when I am looking for something. (3) easy to import to a database if required
I one script on all my instances to setup audit.
begin
set nocount on
declare @server_audit_groups table (saname nvarchar(100))
declare @loc varchar(500)
declare @sql varchar (500)
declare @okay int
declare @saname nvarchar(100)
insert into @server_audit_groups values ('APPLICATION_ROLE_CHANGE_PASSWORD_GROUP')
insert into @server_audit_groups values ('AUDIT_CHANGE_GROUP')
insert into @server_audit_groups values ('BACKUP_RESTORE_GROUP')
insert into @server_audit_groups values ('BROKER_LOGIN_GROUP')
insert into @server_audit_groups values ('DATABASE_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_LOGOUT_GROUP')
insert into @server_audit_groups values ('DATABASE_MIRRORING_LOGIN_GROUP')
-- insert into @server_audit_groups values ('DATABASE_OBJECT_ACCESS_GROUP') -- will genrate too much info
-- insert into @server_audit_groups values ('DATABASE_OBJECT_CHANGE_GROUP') -- will genrate too much info
insert into @server_audit_groups values ('DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_OBJECT_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_OPERATION_GROUP')
insert into @server_audit_groups values ('DATABASE_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_PRINCIPAL_CHANGE_GROUP')
insert into @server_audit_groups values ('DATABASE_PRINCIPAL_IMPERSONATION_GROUP')
insert into @server_audit_groups values ('DATABASE_ROLE_MEMBER_CHANGE_GROUP')
insert into @server_audit_groups values ('DBCC_GROUP')
insert into @server_audit_groups values ('FAILED_DATABASE_AUTHENTICATION_GROUP')
insert into @server_audit_groups values ('FAILED_LOGIN_GROUP')
insert into @server_audit_groups values ('FULLTEXT_GROUP')
insert into @server_audit_groups values ('LOGIN_CHANGE_PASSWORD_GROUP')
insert into @server_audit_groups values ('LOGOUT_GROUP')
--insert into @server_audit_groups values ('SCHEMA_OBJECT_ACCESS_GROUP') -- will genrate too much info
insert into @server_audit_groups values ('SCHEMA_OBJECT_CHANGE_GROUP')
insert into @server_audit_groups values ('SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OBJECT_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OBJECT_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_OPERATION_GROUP')
insert into @server_audit_groups values ('SERVER_PERMISSION_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_PRINCIPAL_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_PRINCIPAL_IMPERSONATION_GROUP')
insert into @server_audit_groups values ('SERVER_ROLE_MEMBER_CHANGE_GROUP')
insert into @server_audit_groups values ('SERVER_STATE_CHANGE_GROUP')
insert into @server_audit_groups values ('SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP')
insert into @server_audit_groups values ('SUCCESSFUL_LOGIN_GROUP')
insert into @server_audit_groups values ('TRACE_CHANGE_GROUP')
insert into @server_audit_groups values ('USER_CHANGE_PASSWORD_GROUP')
insert into @server_audit_groups values ('USER_DEFINED_AUDIT_GROUP')
SELECT @loc = replace(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(255)),'ERRORLOG','')
declare c1 cursor for select saname from @server_audit_groups
open c1
fetch c1 into @saname
while @@FETCH_STATUS = 0
--select @okay = count (*) FROM [master].[sys].[dm_server_audit_status]
--print @okay
--if @okay = 0
begin
set @sql='CREATE SERVER AUDIT [' + @saname +'] TO FILE (FILEPATH = '''
set @sql = @sql + @loc
set @sql = @sql + ''',MAXSIZE = 2048 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF )'
print @sql
--EXEC (@sql)
set @sql = 'alter server audit ['+@saname +'] with (state = ON)'
print @sql
set @sql = 'CREATE SERVER AUDIT SPECIFICATION ['+ @saname +'] FOR SERVER AUDIT ['+@saname +'] ADD ('+@saname +') WITH (STATE = ON)'
PRINT @sql
fetch next from c1 into @saname
end
set nocount off
close c1
deallocate c1
end