A jobSql Job was changed. The questions are: When? by Who?
We suppose you always have a copy of yours jobs.
** Check the SQL Script for shared folder, your e-mail and SMTP server.
Delete old audit configuration – make sure you want to do that.
1
2print @@servername
3
4USE [master]
5
6GO
7
8BEGIN TRY ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;
9
10BEGIN TRY DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification]; END TRY BEGIN CATCH END CATCH;
11
12GO
13
14BEGIN TRY ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;
15
16BEGIN TRY DROP SERVER AUDIT [ServerAudit]; END TRY BEGIN CATCH END CATCH;
17
18GO
19
20--xp_cmdshell 'DIR L:Audit*'
21
22exec xp_cmdshell 'MD C:Audit*';
23
24exec xp_cmdshell 'DEL C:Audit*.sqlaudit';
25
26GO
Create an Audit on a network share
1
2PRINT '-- Step 1 - Create an Audit on a network share'
3
4USE [master];
5
6GO
7
8CREATE SERVER AUDIT [ServerAudit]
9
10TO FILE
11
12( FILEPATH = N'C:Audit'
13
14 ,MAXSIZE = 100 MB
15
16 ,MAX_FILES = 3500
17
18 ,RESERVE_DISK_SPACE = OFF
19
20)
21
22WITH
23
24( QUEUE_DELAY = 1000
25
26 ,ON_FAILURE = CONTINUE
27
28)
29
30GO
31
32Go
33
34PRINT '-- Step 2 - Enable the Audit'
35
36ALTER SERVER AUDIT [ServerAudit] WITH (STATE=ON);
37
38Go
39
40Go
41
42-- Step 3 - Create a Server Audit Specification
43
44CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [ServerAudit]
45
46--ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
47
48--ADD (AUDIT_CHANGE_GROUP),
49
50--ADD (BACKUP_RESTORE_GROUP),
51
52--ADD (BROKER_LOGIN_GROUP),
53
54--ADD (DATABASE_CHANGE_GROUP),
55
56--ADD (DATABASE_LOGOUT_GROUP),
57
58--ADD (DATABASE_MIRRORING_LOGIN_GROUP),
59
60--ADD (DATABASE_OBJECT_ACCESS_GROUP),
61
62--ADD (DATABASE_OBJECT_CHANGE_GROUP),
63
64--ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
65
66--ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
67
68--ADD (DATABASE_OPERATION_GROUP),
69
70--ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
71
72--ADD (DATABASE_PERMISSION_CHANGE_GROUP),
73
74--ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
75
76--ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
77
78--ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
79
80--ADD (DBCC_GROUP),
81
82--ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
83
84--ADD (FAILED_LOGIN_GROUP),
85
86--ADD (FULLTEXT_GROUP),
87
88--ADD (LOGIN_CHANGE_PASSWORD_GROUP),
89
90--ADD (LOGOUT_GROUP),
91
92--ADD (SCHEMA_OBJECT_ACCESS_GROUP),
93
94--ADD (SCHEMA_OBJECT_CHANGE_GROUP),
95
96--ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
97
98--ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
99
100--ADD (SERVER_OBJECT_CHANGE_GROUP),
101
102--ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
103
104--ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
105
106--ADD (SERVER_OPERATION_GROUP),
107
108--ADD (SERVER_PERMISSION_CHANGE_GROUP),
109
110--ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
111
112--ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
113
114--ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
115
116--ADD (SERVER_STATE_CHANGE_GROUP),
117
118--ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
119
120--ADD (SUCCESSFUL_LOGIN_GROUP),
121
122--ADD (TRACE_CHANGE_GROUP),
123
124--ADD (USER_CHANGE_PASSWORD_GROUP),
125
126ADD (USER_DEFINED_AUDIT_GROUP)
127
128WITH (STATE = ON);
129
130GO
Log or send e-mail when a job was changed.
1
2PRINT '-- Create/Update trigger [trig_sysjobs_log] ON [dbo].[sysjobs]'
3USE [msdb]
4GO
5BEGIN TRY DROP TRIGGER [dbo].[trig_sysjobs_log]; END TRY BEGIN CATCH END CATCH;
6SET ANSI_NULLS ON
7GO
8SET QUOTED_IDENTIFIER OFF
9GO
10SET ANSI_NULLS ON
11GO
12SET QUOTED_IDENTIFIER ON
13GO
14-- =============================================
15-- Description: Audit/Log jobs changements
16-- =============================================
17CREATE TRIGGER [dbo].[trig_sysjobs_log]
18ON [dbo].[sysjobs]
19FOR INSERT, UPDATE, DELETE
20AS
21BEGIN
22 SET NOCOUNT ON
23
24--#region DECLARE variables
25
26 DECLARE @bodyAudit nvarchar(4000);
27 DECLARE @bodyEvent nvarchar(255);
28 DECLARE @bodyEmail nvarchar(max);
29 DECLARE @subjectEmail nvarchar(255);
30
31 DECLARE @activity varchar(20)
32 DECLARE @jobName varchar(255);
33 DECLARE @jobNameDeleted varchar(255);
34 SET @JobName = '';
35 SET @jobNameDeleted = '';
36
37 DECLARE @systemUser varchar(100)
38 DECLARE @datelog VARCHAR(50);
39 DECLARE @SUSER_ID varchar(20);
40 DECLARE @originalLogin nvarchar(256);
41 DECLARE @hostname nvarchar(128);
42 DECLARE @programName nvarchar(max);
43
44--#endregion DECLARE variables
45
46--#region Create message
47
48 SET @systemUser = SYSTEM_USER;
49 SET @datelog = GETUTCDATE();
50 SET @SUSER_ID = CAST(SUSER_ID() as varchar(20));
51 SET @originalLogin = ORIGINAL_LOGIN();
52 SET @hostname = HOST_NAME();
53 SET @programName= PROGRAM_NAME();
54
55 if exists(SELECT * from inserted)
56 begin
57 if exists (SELECT * from deleted)
58 BEGIN
59 SET @activity = 'UPDATE';
60 SELECT @jobNameDeleted = name from deleted;
61 END
62 else
63 BEGIN
64 SET @activity = 'INSERT';
65 END
66 SELECT @jobName = name from inserted;
67 end
68 else
69 begin
70 SET @activity = 'DELETE';
71 SELECT @jobName = name from deleted;
72 end
73
74 -- Audit
75 SET @bodyAudit = 'AuditJob' + CHAR(13) + CHAR(10);;
76 SET @bodyAudit = @bodyAudit + N'-Job : ' + @JobName + CHAR(13) + CHAR(10);
77 IF ( @jobNameDeleted NOT IN ( @JobName , '') ) SET @bodyAudit = @bodyAudit + N'-Job Deleted : ' + @jobNameDeleted + CHAR(13) + CHAR(10);
78 SET @bodyAudit = @bodyAudit + N'-Activity : ' + @activity + CHAR(13) + CHAR(10);
79 SET @bodyAudit = @bodyAudit + N'-Original Login : ' + @originalLogin + CHAR(13) + CHAR(10);
80 IF (@originalLogin<>@systemUser) SET @bodyAudit = @bodyAudit + N'-System User : ' + @systemUser + CHAR(13) + CHAR(10);
81 SET @bodyAudit = @bodyAudit + N'-Date UTC : ' + @datelog + CHAR(13) + CHAR(10);
82 SET @bodyAudit = @bodyAudit + N'-Host name : ' + @hostname + CHAR(13) + CHAR(10);
83 SET @bodyAudit = @bodyAudit + N'-SUSER_ID : ' + @SUSER_ID + CHAR(13) + CHAR(10);
84 SET @bodyAudit = @bodyAudit + N'-Program Name : ' + @programName + CHAR(13) + CHAR(10);
85 BEGIN TRY
86 EXEC sp_audit_write @user_defined_event_id = 169 , @succeeded = 0, @user_defined_information = @bodyAudit;
87 END TRY BEGIN CATCH END CATCH;
88
89--#endregion Create message
90
91--#region Send email
92
93 -- Check if database mail service is configurated with a default profil
94 IF (EXISTS ( SELECT prinprof.is_default
95 FROM msdb.sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
96 WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid) AND
97 prof.profile_id = prinprof.profile_id AND prinprof.is_default = 1
98 )
99 )
100 BEGIN
101 -- Send email
102 BEGIN TRY
103
104 SET @bodyEmail = @bodyAudit;
105 SET @subjectEmail = @activity+ ' job ['+ @jobName + ']'
106 EXEC msdb.dbo.sp_send_dbmail
107 @recipients = 'Youremail@mydomain.com',
108 @subject = @subjectEmail,
109 @body = @bodyEmail,
110 @from_address = 'noreplay@mydomain.com',
111 @reply_to = 'noreplay@mydomain.com';
112
113 END TRY BEGIN CATCH END CATCH;
114 END
115--#endregion Send email
116
117--#region Create Event Log
118
119 SET @bodyEvent = REPLACE(@bodyAudit, ' ',' ');
120 SET @bodyEvent = REPLACE(@bodyAudit, ' ',' ');
121 SET @bodyEvent = SUBSTRING(@bodyEvent,0, 255);
122
123 RAISERROR (@bodyEvent, -- Message text.
124 10, -- Severity,
125 1 -- State,
126 ) WITH LOG;
127
128--#endregion Create Event Log
129
130END
131GO
132GO
Check your audit
1
2SELECT top 10 * FROM sys.fn_get_audit_file('C:Audit*', NULL, NULL) ORDER BY 1 DESC;
Sources:
Comments