question

DevendraSahu-0306 avatar image
0 Votes"
DevendraSahu-0306 asked ErlandSommarskog commented

how to create alert when any job add, modify or delete in sql server

I have a company they use SQL Server 2016 SE.

here multiple DBA owner and they create there requirement basis job and it modify, or delete.
that reason we have multiple job in SQL Server, i m not properly monitor to all job.
this reason I want to alert when any job create, modify or delete in SQL Server.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

I would try to create the triggers on the table [msdb].[dbo].[sysjobs] for INSERT, UPDATE and DELETE so that the notification emails would be sent out when the job is created, updated or deleted.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered DevendraSahu-0306 commented

Hi DevendraSahu-0306,

In addition, you can use SQL Server Audit to create a Database Audit Specification for sp_add_job,
sp_update_job, sp_delete_job, and dbo.sysjobs in the msdb database. Please refer to Audit SQL Server Jobs for more details.
And here is an article which might be helpful.

Best Regards,
Amelia


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

did you have code please share

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ErlandSommarskog commented

Hi DevendraSahu-0306,
Please check:

 USE [master]
 GO
 CREATE SERVER AUDIT [agrentjobAudit]
 TO FILE 
 (    FILEPATH = N'E:\Audits'
     ,MAXSIZE = 0 MB
     ,MAX_ROLLOVER_FILES = 2147483647
     ,RESERVE_DISK_SPACE = OFF
 ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
 GO
 ALTER SERVER AUDIT [agrentjobAudit] WITH (STATE = ON);  
 GO
    
 USE [msdb]
 GO
 CREATE DATABASE AUDIT SPECIFICATION [AgentJob]
 FOR SERVER AUDIT [agrentjobAudit]
 ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
 ADD (EXECUTE ON OBJECT::[dbo].[sp_update_job] BY [dbo]),
 ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
 ADD (DELETE ON OBJECT::[dbo].[sysjobs] BY [dbo]),
 ADD (INSERT ON OBJECT::[dbo].[sysjobs] BY [dbo]),
 ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [dbo])
 GO
 ALTER DATABASE AUDIT SPECIFICATION [AgentJob] WITH (STATE = ON);  
 GO

Then you can view SQL Server audit log in SSMS Object Explorer->expand the Security-> Audits->
right-click the audit log that you want to view and select View Audit Log.

Best Regards,
Amelia


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AmeliaGu-msft

Hello Sir,

it's Good for me but i want to mail notification (i already Configure Mail Profile).

0 Votes 0 ·

If you want a mail, you would either have to create a trigger as Guoxiong suggested, or write a program that regularly polls the audit file and send you mail.

Personally, I would consider the latter. I can't say that I like the idea of adding triggers in msdb.

0 Votes 0 ·