When we are creating a job in SQL server agent, all job details are stored in the SYSJOBS system table in msdb Database. With the help of the enabled flag, we will come to know that this job is enabled or disabled. (1-enabled and 0- disabled)
Select * from sysjobs
Below is the definition of this table.
If we see the definition of this table, we will not get any column where it tracks the modified by (user). It only tracks the modified date.
SQL server doesn't capture job modification. To track the modification details, we can create a trigger in system table SYSJOBS and insert this record in the audit table.
I am creating an audit table on the MSDB database where I will store all the audit details.
|
create table Jobs_Enable_Audit
(ID int identity(1,1),
Job_Name varchar(50),
Job_Created_Date datetime,
Job_Current_Status int,
Server_Name varchar(50),
Job_Modify_date datetime,
Job_Modify_by varchar(50))
|
Table created successfully.
Now we will create a trigger on the SYSJOBS table.
Read: Trigger in sql server
|
Create trigger Audit_Agent_Job_update on MSDB.dbo.sysjobs
for Update
AS
declare @Job_Name varchar(50), @Job_Created_Date datetime, @Job_Current_Status int,
@Server_Name varchar(50), @Job_Modify_date datetime,@Job_Modify_by VARCHAR(50);
select @Job_Name=i.name from inserted i;
select @Job_Created_Date=i.date_created from inserted i;
select @Job_Current_Status =i.enabled from inserted i;
select @Server_Name = @@SERVERNAME;
select @Job_Modify_date = getdate();
Select @Job_Modify_by=SUSER_NAME();
BEGIN
Insert into SQL_SERVER_AGENT_AUDIT
(Job_Name,Job_Created_Date,Job_Current_Status,Server_Name,
Job_Modify_date,Job_Modify_by) values
(@Job_Name,@Job_Created_Date,@Job_Current_Status,
@Server_Name,@Job_Modify_date,@Job_Modify_by)
END
|
A trigger is created successfully.
Now we are ready for the test.
There is a job named as Insert_record_on_EMP_tbl , currently this is enabled job.
See in the database.
We can also see them when we are editing an existing job. Double clicked on the job. A new window will be open as below.
Enabled checked it means this job is currently enabled.
Now I am doing uncheck of Enabled checked box.
Click ok.
Now this job is disabled.
This job is disabled. This job is marked as red.
Now we see the audit table.
Let’s enable this job again.
Now the job is enabled.
See the audit table.
