Audit jobs

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.

print @@servername

USE [master]

GO

BEGIN TRY ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP  SERVER AUDIT SPECIFICATION [ServerAuditSpecification];                    END TRY BEGIN CATCH END CATCH;

GO

BEGIN TRY ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP  SERVER AUDIT [ServerAudit];                    END TRY BEGIN CATCH END CATCH;

GO

--xp_cmdshell 'DIR L:Audit*'

exec xp_cmdshell 'MD C:Audit*';

exec xp_cmdshell 'DEL C:Audit*.sqlaudit';

GO

Create an Audit on a network share

PRINT '-- Step 1 - Create an Audit on a network share'

USE [master];

GO

CREATE SERVER AUDIT [ServerAudit]

TO FILE

(     FILEPATH  = N'C:Audit'

     ,MAXSIZE   = 100 MB

     ,MAX_FILES = 3500

     ,RESERVE_DISK_SPACE = OFF

)

WITH

(     QUEUE_DELAY = 1000

     ,ON_FAILURE  = CONTINUE

)

GO

Go

PRINT '-- Step 2 - Enable the Audit'

ALTER SERVER AUDIT [ServerAudit] WITH (STATE=ON);

Go

Go

-- Step 3 - Create a Server Audit Specification

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [ServerAudit]

--ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),

--ADD (AUDIT_CHANGE_GROUP),

--ADD (BACKUP_RESTORE_GROUP),

--ADD (BROKER_LOGIN_GROUP),

--ADD (DATABASE_CHANGE_GROUP),

--ADD (DATABASE_LOGOUT_GROUP),

--ADD (DATABASE_MIRRORING_LOGIN_GROUP),

--ADD (DATABASE_OBJECT_ACCESS_GROUP),

--ADD (DATABASE_OBJECT_CHANGE_GROUP),

--ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (DATABASE_OPERATION_GROUP),

--ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

--ADD (DATABASE_PERMISSION_CHANGE_GROUP),

--ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

--ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),

--ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

--ADD (DBCC_GROUP),

--ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

--ADD (FAILED_LOGIN_GROUP),

--ADD (FULLTEXT_GROUP),

--ADD (LOGIN_CHANGE_PASSWORD_GROUP),

--ADD (LOGOUT_GROUP),

--ADD (SCHEMA_OBJECT_ACCESS_GROUP),

--ADD (SCHEMA_OBJECT_CHANGE_GROUP),

--ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_OBJECT_CHANGE_GROUP),

--ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_OPERATION_GROUP),

--ADD (SERVER_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_PRINCIPAL_CHANGE_GROUP),

--ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),

--ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

--ADD (SERVER_STATE_CHANGE_GROUP),

--ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),

--ADD (SUCCESSFUL_LOGIN_GROUP),

--ADD (TRACE_CHANGE_GROUP),

--ADD (USER_CHANGE_PASSWORD_GROUP),

ADD (USER_DEFINED_AUDIT_GROUP)

WITH (STATE = ON);

GO

Log or send e-mail when a job was changed.

 

PRINT '-- Create/Update trigger [trig_sysjobs_log] ON [dbo].[sysjobs]'
USE [msdb]
GO
BEGIN TRY DROP TRIGGER [dbo].[trig_sysjobs_log]; END TRY BEGIN CATCH END CATCH;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:    Audit/Log jobs changements
-- =============================================
CREATE TRIGGER [dbo].[trig_sysjobs_log]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
     SET NOCOUNT ON

--#region DECLARE variables

    DECLARE @bodyAudit nvarchar(4000);
     DECLARE @bodyEvent nvarchar(255);
     DECLARE @bodyEmail nvarchar(max);
     DECLARE @subjectEmail nvarchar(255);
    
     DECLARE @activity       varchar(20)
     DECLARE @jobName        varchar(255);
     DECLARE @jobNameDeleted varchar(255);
     SET @JobName = '';
     SET @jobNameDeleted = '';

    DECLARE @systemUser varchar(100)
     DECLARE @datelog          VARCHAR(50);
     DECLARE @SUSER_ID         varchar(20);
     DECLARE @originalLogin    nvarchar(256);
     DECLARE @hostname    nvarchar(128);
     DECLARE @programName nvarchar(max);

--#endregion DECLARE variables

--#region Create message

    SET @systemUser = SYSTEM_USER;
     SET @datelog    = GETUTCDATE();
     SET @SUSER_ID   = CAST(SUSER_ID() as varchar(20));
     SET @originalLogin   = ORIGINAL_LOGIN();
     SET @hostname   = HOST_NAME();
     SET @programName= PROGRAM_NAME();

    if exists(SELECT * from inserted)
     begin
         if exists (SELECT * from deleted)
         BEGIN
             SET @activity = 'UPDATE';
             SELECT @jobNameDeleted = name from deleted;
         END
         else
         BEGIN
             SET @activity = 'INSERT';
         END
         SELECT @jobName = name from inserted;
     end
     else
     begin
         SET @activity = 'DELETE';
         SELECT @jobName = name from deleted;
     end
    
     -- Audit
     SET @bodyAudit = 'AuditJob' + CHAR(13) + CHAR(10);;
     SET @bodyAudit = @bodyAudit + N'-Job : ' + @JobName + CHAR(13) + CHAR(10);
     IF ( @jobNameDeleted NOT IN ( @JobName , '') ) SET @bodyAudit = @bodyAudit + N'-Job Deleted : ' + @jobNameDeleted + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Activity : ' + @activity + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Original Login : ' + @originalLogin + CHAR(13) + CHAR(10);   
     IF (@originalLogin<>@systemUser) SET @bodyAudit = @bodyAudit + N'-System User : ' + @systemUser + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Date UTC : ' + @datelog + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Host name : ' + @hostname + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-SUSER_ID : ' + @SUSER_ID + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Program Name : ' + @programName + CHAR(13) + CHAR(10);
     BEGIN TRY
         EXEC sp_audit_write @user_defined_event_id =  169 , @succeeded =  0, @user_defined_information = @bodyAudit;
     END TRY BEGIN CATCH END CATCH;

--#endregion Create message

--#region Send email

    -- Check if database mail service is configurated with a default profil
     IF (EXISTS ( SELECT prinprof.is_default
                  FROM msdb.sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
                  WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid) AND
                  prof.profile_id = prinprof.profile_id    AND prinprof.is_default = 1
                )
        )
     BEGIN
         -- Send email
         BEGIN TRY

            SET @bodyEmail = @bodyAudit;
             SET @subjectEmail = @activity+ ' job ['+ @jobName + ']'
             EXEC msdb.dbo.sp_send_dbmail
                 @recipients        = 'Youremail@mydomain.com',
                 @subject        = @subjectEmail,
                 @body            = @bodyEmail,
                 @from_address = 'noreplay@mydomain.com',
                 @reply_to = 'noreplay@mydomain.com';

        END TRY BEGIN CATCH END CATCH;
     END
--#endregion Send email

--#region Create Event Log

    SET @bodyEvent = REPLACE(@bodyAudit, '   ',' ');
     SET @bodyEvent = REPLACE(@bodyAudit, '  ',' ');
     SET @bodyEvent = SUBSTRING(@bodyEvent,0, 255);

    RAISERROR (@bodyEvent, -- Message text.
            10, -- Severity,
            1 -- State,
            ) WITH LOG;
    
--#endregion Create Event Log

END
GO
GO

Check your audit

SELECT top 10 * FROM sys.fn_get_audit_file('C:Audit*', NULL, NULL) ORDER BY 1 DESC;

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-server-audit-specification?view=sql-server-ver15