DDL event for job changes

Question

DDL event for job changes

petitpere

SSCrazy

Points: 2520
More actions
November 25, 2016 at 3:39 am

#332051

Hi,
I have a DDL Audit trigger which traces all the following events:
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
ALTER_SCHEMA, RENAME
Unfortunatly it does not report the changes done to a job. (Something/somebody periodically alters the script of a job and I can't find out the reason).
I'd like to include the job creation/change/drop events to my Audit trigger but can't find which event is concerned.
Any help will be much appreciated.
Thanks a lot ....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think job changes are actually DML changes, and not DDL changes; those are insert/update/delete to the tables in msdb: dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules for example.

you could put DML triggers on those tables, (maybe? since they are semi-system tables?), to capture changes; or maybe use some of the built in tools like change tracking , cdc, etc if your SQL version supports it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

petitpere SSCrazy Points: 2520 More actions · Answer 2

Thanks Lowell for pointing me out on a DML trigger on the sysjobs[...] tables.

I'll give it a try and let you know the results.

Now, if a DDL event exists for amendments to jobs, I'll prefer that approach ...

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 3

SSMS normally makes modifications to the SQL Server scheduled jobs through calls to sp_add_job, sp_updatejob, sp_add_jobstep, sp_update_jobstep etc in the msdb database. There's a whole "chapter" about the SQL Server Agent stored procedures in MSDN.

The bad news is that there is currently no mechanism to create a sort of "ON EXECUTE" trigger for a stored procedure, so you will be stuck with triggers on the job- & schedule-related tables in msdb.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Lowell SSC Guru Points: 323504 More actions · Answer 4

Thomas makes a good paint on the procedures being the way the GUI makes job changes; that means besides triggers, you could also modify the procedures themselves so that they log job changes into a new table, since those procs are also under the dbo schema.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 100949 More actions · Answer 5

I'd urge you not to create triggers on system tables, particularly job tables.

The sysjobs table does have a "date_modified" column. Presumably that column is updated whenever any step within that job changes, but naturally you'd have to test it to be sure. sysschedules also has a date_modified, although sysjobschedules doesn't, although it would be very nice to have it there as well.

Thus, I'd recommend instead that you create a job or some other mechanism to check the modified date(s) of the jobs. And/or just capture job code and do a full compare to see if it's changed rather than creating triggers on system tables :unsure:

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.