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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.