July 22, 2008 at 12:42 pm
Can any please tell how audit the below mentioned activity :-
a) Say at time X if a user "A" DISABLES/ENABLES the job . Need to know how to audit the particular user name "A" and Time "X" which made changes on the job.
July 22, 2008 at 1:37 pm
I believe you can set up a trace to track that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 1:50 pm
Not that I am really advocating this, but you could put a trigger on the sysjobs table in msdb that logs changes. There actually is already an insert, update trigger on the table that you could modify as well. Again, I'm not necessarily recommending it, just saying it is possible. You could also do as G mentions and set up a server-side trace that will log it to a file. You'd want to filter on database and object id so you were only getting hits on msdb and sysjobs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2008 at 1:53 pm
I recommend against messing around with the code in msdb. Don't change the trigger, don't add one.
Manually modifying that kind of stuff in system databases always bothers me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 2:02 pm
GSquared (7/22/2008)
I recommend against messing around with the code in msdb. Don't change the trigger, don't add one.Manually modifying that kind of stuff in system databases always bothers me.
I'm not saying do it, I'm just giving an option. I wouldn't do it either, but some people probably would. I've read a bunch of threads/articles where people are changing the sp's created for replication and I wouldn't do that either.
A trace would work fine although you would probably end up with a lot more data than you need. I'd probably start up Profiler, edit a job and then use that information to setup very specific filters so I only get the data I want.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2008 at 2:14 pm
Jack Corbett (7/22/2008)
GSquared (7/22/2008)
I recommend against messing around with the code in msdb. Don't change the trigger, don't add one.Manually modifying that kind of stuff in system databases always bothers me.
I'm not saying do it, I'm just giving an option. I wouldn't do it either, but some people probably would. I've read a bunch of threads/articles where people are changing the sp's created for replication and I wouldn't do that either.
A trace would work fine although you would probably end up with a lot more data than you need. I'd probably start up Profiler, edit a job and then use that information to setup very specific filters so I only get the data I want.
I would expect you to do the right thing on it. I'm merely warning random people wandering around on the web page.
There are people who can, certainly, handle the system databases correctly and customize them to do things they need. My warning is merely for anyone who doesn't realize they're playing with fire.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 5:03 pm
I'm attaching code for a server side trace. It will grab any updates to the sysjobs table and anyone who runs sp_update_job (which is executed when you enable/disable a job via SSMS). Once you run the query, it will run the trace in the background and place output into C:\DBA_MonitorJobs_ .trc. It may not be exactly what you want, but at least it will give you a starting point. I've used this code as a template for setting up multiple server side traces (I put them into jobs).
Note that I was able to create this code thanks to BOL and other posting on the web. 🙂
-- Declare variables
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxFileSize bigint
DECLARE @fileName NVARCHAR(128)
DECLARE @on bit
declare @runtime varchar(12)
select @runtime = convert(varchar(20),getdate(),112) + right('00' +convert(varchar(2),datepart(hh, getdate())),2) +right('00' +convert(varchar(2),datepart(mi, getdate())),2)
--select @runtime
set @TraceID = 5
-- Set values
SET @maxFileSize = 15
SET @fileName = 'C:\DBA_MonitorJobs_' + @runtime
SET @on = 1
-- Create trace
/*
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] ''trace_file''
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] ''stop_time'' ]
[ , [ @filecount = ] ''max_rollover_files'' ]
*/
EXEC @rc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, NULL, 10
-- If error end process
IF (@rc != 0) GOTO error
-- Set the events and data to collect
--
exec sp_trace_setevent @TraceId,40,7,@on
exec sp_trace_setevent @TraceId,40,8,@on
exec sp_trace_setevent @TraceId,40,64,@on
exec sp_trace_setevent @TraceId,40,1,@on
exec sp_trace_setevent @TraceId,40,9,@on
exec sp_trace_setevent @TraceId,40,49,@on
exec sp_trace_setevent @TraceId,40,6,@on
exec sp_trace_setevent @TraceId,40,10,@on
exec sp_trace_setevent @TraceId,40,14,@on
exec sp_trace_setevent @TraceId,40,26,@on
exec sp_trace_setevent @TraceId,40,3,@on
exec sp_trace_setevent @TraceId,40,11,@on
exec sp_trace_setevent @TraceId,40,35,@on
exec sp_trace_setevent @TraceId,40,4,@on
exec sp_trace_setevent @TraceId,40,12,@on
exec sp_trace_setevent @TraceId,40,5,@on
exec sp_trace_setfilter @TraceId,NULL,0,0,@on
-- Set Filters
-- trace only modifications to the job table
exec sp_trace_setfilter @TraceId,1,0,6,N'update %sysjobs%'
exec sp_trace_setfilter @TraceId,1,1,6,N'%sp_update_job%'
-- Start the trace
exec sp_trace_setstatus @TraceId,1
-- display trace id for future references
SELECT TraceID=@TraceID
GOTO finish
-- error trap
error:
SELECT ErrorCode=@rc
-- exit
finish:
-- display Trace information
--select * from ::fn_trace_getinfo(default)
GO
/*
-- Stop the trace (assuming traceid = 4)
EXEC sp_trace_setstatus 4, 0
-- delete the trace (assuming traceid = 4)
EXEC sp_trace_setstatus 4, 2
-- Start the trace (assuming traceid = 5)
EXEC sp_trace_setstatus 4, 1
-- display Trace information (can output this to a logfile in sqlagent)
select * from ::fn_trace_getinfo(default)
*/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply