November 30, 2017 at 11:15 am
What am I doing wrong here? The only thing that I see in the audit log is my account enabling and disabling the audits.
:setvar DBName "MyDatabase"
-- CREATE SERVER AUDIT
USE [master]
GO
CREATE SERVER AUDIT [CaptureUserActionAudit]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
ALTER SERVER AUDIT CaptureUserActionAudit WITH (STATE = ON)
GO
--CREATE AUDITABLE ROLE
USE [$(DBName)]
GO
CREATE ROLE [DbAuditable]
GO
--ADD AUDITABLE USERS TO THIS ROLE
ALTER ROLE DbAuditable ADD MEMBER [MyDomain\MyAccount]
CREATE DATABASE AUDIT SPECIFICATION [DatabaseUserActionAuditSpecification]
FOR SERVER AUDIT [CaptureUserActionAudit]
ADD (SELECT ON DATABASE::[$(DBName)] BY [DbAuditable])
WITH (STATE = ON)
GO
November 30, 2017 at 12:56 pm
not sure, wrote this script long time ago -- modify it as you like
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
set nocount on
declare @t as table (cmds varchar(8000))
insert into @t
exec sp_msforeachdb "use [?];SELECT 'use ?;alter database audit specification ' + name + ' WITH (STATE = OFF);' FROM sys.database_audit_specifications"
insert into @t
exec sp_msforeachdb "use [?];SELECT 'use ?;drop database audit specification ' + name + ' ;' FROM sys.database_audit_specifications"
SELECT 'USE MASTER; ALTER SERVER AUDIT [' + cast(name as nvarchar(250)) + '] WITH (STATE = OFF);'
FROM [master].[sys].[server_audits]
select 'use master; ALTER SERVER AUDIT SPECIFICATION [' + cast(name as nvarchar(250)) + '] WITH (STATE = OFF);'
from [master].[sys].[server_audit_specifications]
select 'use master; drop SERVER AUDIT SPECIFICATION [' + cast(name as nvarchar(250)) + '];'
from [master].[sys].[server_audit_specifications]
SELECT 'USE MASTER; DROP SERVER AUDIT [' + cast(name as nvarchar(250)) + '] ;'
FROM [master].[sys].[server_audits]
select cmds as [--cmds] from @t
delete from @t
insert into @t
select 'USE MASTER; Create Server Audit ' + cast(name as nvarchar(250)) + '_SIDU_Audit to file (filepath=''Y:\Audit\'',MAXSIZE=1024 MB,MAX_FILES = 5,RESERVE_DISK_SPACE=OFF) WHERE [schema_name]<>''sys'';'
-- AND [server_principal_name] <> ''newuser'' AND [server_principal_name] <> ''DOMAIN\user1'' AND [server_principal_name] <> ''DOMAIN\user2'' AND [server_principal_name] <> ''user3'';'
from sysdatabases where dbid >4
insert into @t
select 'USE MASTER; ALTER SERVER AUDIT ' + cast(name as nvarchar(250)) + '_SIDU_Audit WITH (STATE=ON);'
from sysdatabases where dbid >4
--select SERVERPROPERTY('ErrorLogFileName') -- can be used for filelocation
insert into @t
select 'USE ['+ cast(name as nvarchar(250)) + ']; CREATE DATABASE AUDIT SPECIFICATION [' + cast(name as nvarchar(250)) + '_SIDU_DBAudit] FOR SERVER AUDIT [' + cast(name as nvarchar(250)) + '_SIDU_Audit] ADD (select,insert,delete,update ON database::[' + cast(name as nvarchar(250)) + '] BY [public]) WITH (STATE=ON);'
from sysdatabases where dbid >4
select cmds as [--Genrate Audit Commands] from @t -- where cmds not like '%SharePoint%'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply