sql-server-toolbox/audit setup.sql at master · SDPMrem71/sql-server-toolbox · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

--TODOs
--1. Specify events and logins/groups to be captured for Database Audit
--	 As configured only captures writes. Add a similar pattern for ADD(SELECT ... to capture all reads.
--2. Set filter on databases to be audited

--If lines are broken and do not paste correctly,
--in SSMS, use Tools -> Options -> Query Results -> SQL Server -> Results to Grid -> Retain CR/LF on copy or save
--then open/close SSMS

--You may also have to change the "Maximum Number of Characters displayed in each column from the default.
--in SSMS, use Tools -> Options -> Query Results -> SQL Server -> Results to Text window
--then open/close SSMS window

use master
GO
SET NOCOUNT ON
GO
SELECT
'USE [master];
GO
CREATE SERVER AUDIT ['+replace(@@SERVERNAME,'\','')+'-Audit]
TO APPLICATION_LOG --write to the Application Event Log
WITH
(	QUEUE_DELAY = 5000
	,ON_FAILURE = CONTINUE
);
go
CREATE SERVER AUDIT SPECIFICATION ['+replace(@@SERVERNAME,'\','')+'-Audit-Spec]
FOR SERVER AUDIT ['+replace(@@SERVERNAME,'\','')+'-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP), --Important: This event is raised for any DDL in any database.
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = OFF);
GO'

SELECT
--TODO 1. specify events and logins/groups to be captured for Database Audit
--As configured only captures writes. Add a similar pattern for ADD(SELECT ... to capture all reads.

--catch all known admin users, regardless of whether or not they have dbo right now
--Examples:
--ADD (UPDATE ON DATABASE::['+d.name+'] BY [Sparkhound\william.assaf]),
--ADD (DELETE ON DATABASE::['+d.name+'] BY [Sparkhound\william.assaf]),
--ADD (INSERT ON DATABASE::['+d.name+'] BY [Sparkhound\william.assaf]),
--ADD (EXECUTE ON DATABASE::['+d.name+'] BY [Sparkhound\william.assaf]),
--ADD (UPDATE ON DATABASE::['+d.name+'] BY [Sparkhound\Developers]),
--ADD (DELETE ON DATABASE::['+d.name+'] BY [Sparkhound\Developers]),
--ADD (INSERT ON DATABASE::['+d.name+'] BY [Sparkhound\Developers]),
--ADD (EXECUTE ON DATABASE::['+d.name+'] BY [Sparkhound\Developers]),

--catch all database admins, period.
--ADD (UPDATE ON DATABASE::['+d.name+'] BY [dbo]),
--ADD (INSERT ON DATABASE::['+d.name+'] BY [dbo]),
--ADD (DELETE ON DATABASE::['+d.name+'] BY [dbo]),
--ADD (EXECUTE ON DATABASE::['+d.name+'] BY [dbo]),
--ADD (SELECT ON DATABASE::['+d.name+'] BY [dbo])
--WITH (STATE = OFF);

N'USE ['+d.name+'];
GO
CREATE DATABASE AUDIT SPECIFICATION [Database-'+replace(d.name,N' ',N'')+N'-Audit-Spec]
FOR SERVER AUDIT ['+replace(@@SERVERNAME,'\','')+N'-Audit]
--catch all activity, period.
ADD (UPDATE ON DATABASE::['+d.name+N'] BY [public]),
ADD (INSERT ON DATABASE::['+d.name+N'] BY [public]),
ADD (DELETE ON DATABASE::['+d.name+N'] BY [public]),
ADD (EXECUTE ON DATABASE::['+d.name+N'] BY [public]),
ADD (SELECT ON DATABASE::['+d.name+N'] BY [public])
WITH (STATE = OFF);
GO
ALTER DATABASE AUDIT SPECIFICATION [Database-'+replace(d.name,N' ',N'')+N'-Audit-Spec]
FOR SERVER AUDIT ['+replace(@@SERVERNAME,'\','')+N'-Audit]
WITH (STATE = ON);
GO
'
FROM (SELECT name = convert(nvarchar(4000), d.name) from sys.databases d
WHERE d.name not in ('tempdb','msdb','distribution')
--and d.name in (N'a2012db') --this filter for testing only
) d;
--TODO 2. Add any database names here you want to ignore

GO
SELECT '
use master
go
ALTER SERVER AUDIT SPECIFICATION ['+replace(@@SERVERNAME,'\','')+'-Audit-Spec]
WITH (STATE = ON);
GO
ALTER SERVER AUDIT ['+replace(@@SERVERNAME,'\','')+'-Audit]
WITH (STATE = ON);
GO
';

/*

--Cleanup

USE [master]
GO
ALTER SERVER AUDIT [BTR-69NRN32SQL2K16-Audit] WITH (STATE = OFF);
GO
ALTER SERVER AUDIT SPECIFICATION [BTR-69NRN32SQL2K16-Audit-Spec] WITH (STATE = OFF);
GO
USE [a2012db]
GO
ALTER DATABASE AUDIT SPECIFICATION [Database-a2012db-Audit-Spec] WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION [Database-a2012db-Audit-Spec]
GO
USE [master]
GO
DROP SERVER AUDIT SPECIFICATION [BTR-69NRN32SQL2K16-Audit-Spec]
GO
DROP SERVER AUDIT [BTR-69NRN32SQL2K16-Audit]
GO

*/