SQL 2005 Server Agent History - who turned it on and/or who started a specific job

To start:  We had an issue where SQL Agent was turned on when it was turned off (and should not have been turned on).

Is there a way to find who started SQL Server Agent? I know exactly when it was started because of the log file but it does not show WHO started it.

If there is not a way to find that is there a way to find out a SQL Server Agent Job history if it was deleted for a specific date/time it was run?  I am asking this because I have a job that I know ran because I found it in the SQL Agent Log File (just after SQL Agent was started) but the history for the job does not show that the job was run at this specific time (but does show it ran other times before this).

So I want to find who started SQL Agent or who started the job so I can figure out who started agent.  Any help?
LVL 16
brad2575Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>>  I know exactly when it was started because of the log file but it does not show WHO started it.

Check it in Event viewer for that particular time from when your Agent was started to find out who started SQL Agent..
Once Agent is started, it would automatically run Scheduled jobs and hence you wont be able to find out who started your job..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brad2575Author Commented:
The job I am looking for does not have a schedule on it so it would have to have been started manually.
0
pnautaCommented:
If you have auditing on in SQL (failed as well as successful) you should be able to see it in the SQL server logs.

Of course, restricting access to your SQL server as well as inside SQL server (server roles) goes a long way in preventing this kind of thing.  But hindsight is always 20/20
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Ok.. Try the steps given below:

* Right click your job and then click View History
* expand History for that job
* Click on any one of the sub tasks or job steps
* In Message column, you would be able to view under whose account the job is executed..

It would also contain details if job is started under some other user account.
0
brad2575Author Commented:
pnauta,

The person I think started it is trying to hide it and they should have the access, but you are correct and this is only our test server though but still caused some issues.

rrjegan17,

I did that and it is not in the history, that is the problem I am having.  In the logs it shows the job was run (and finished successfully) but it was removed from the job history (or for some reason did not get put in there).
0
pnautaCommented:
Maybe have a look at the SQLAGENT.OUT file.
0
brad2575Author Commented:
I checked that and it is the same thing that is in the event viewer for the SQL Agent.

Any other suggestions?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> but it was removed from the job history (or for some reason did not get put in there).

Do you mean the entire job history is removed out from the View History option or the job steps alone..
Seems like you have limited the Agent History to less no. of records..
Right Click SQL Server Agent in SSMS and then choose Properties --> History.
Choosing any one of the options there impacts or removes older entries of your Agent jobs..

Increase these values and that might help in finding out the user who started the job..
0
HoggZillaCommented:
Have you gone to the msdb.dbo.sysjob... tables? Try looking at sysjobsteps for last run information. Unfortunately, if the job was run under a service account or proxy, you may be out of luck.
HZ
0
brad2575Author Commented:
HoggZilla,

Yes I went there and the entry is gone, but if you delete it from the job history in the interface it is removed from there as well.

rrjegan17,

I can see history for older jobs just not the most recent one that I need to find who started it but the 2 times it ran before that I can see who started it.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If user name is not listed, then it might be a super user account ie., sa account or any other user with sysadmin privileges for sure..
0
brad2575Author Commented:
There is not a user listed because there is no listing for this job at the time it was run, that is the problem I am having.  If I could find the job listing for the date/time it was run I could see if it was an admin account or a specific user account but I can not even see that.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> because there is no listing for this job at the time it was run, that is the problem I am having

If a job is executed, it would definitely be tracked in the Job history..
Try applying SP3 for SQL Server 2005 and try it out once..
0
pnautaCommented:
The data you're looking for is simply in a table in the database.  

Try this:

use msdb
select * from dbo.sysjobhistory

If it's not there, it's been removed by someone with sysadmin rights.  You could try and correlate data from this table to see if values missing from this table are used anywhere else, to prove it's been removed.
0
brad2575Author Commented:
pnauta,

I ran that query and it is not in there, that is what I am trying to find/figure out to see if it was removed.  The person I think that did it does have sysadmin rights.  How would I coorilate it with data else where to see if it was deleted?  I have the logs that say it was run but the logs do not say who did it or anything else.

Is there a way to prove it was deleted other then the logs?  Is there a way to see who deleted it?  If I can see who deleted it I would then assume they were the ones that ran it so if that is all I can find (who deleted it) that would work for me.
0
pnautaCommented:
If your msdb database is in FULL RECOVERY mode, you may analyze the transaction logs.  That may prove records were deleted by hand.

If not, chances are slim to ever prove it.  As msdb is by default in simple recovery mode, you may not even have the logs to analyze.  So, in my book, this one got away.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try running profiler across that table and see whether any inserts or deletes are happening on that table.
0
pnautaCommented:
So, how are you doing in your own CSI episode :-) ?  Was the msdb database in full recovery mode?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Was the msdb database in full recovery mode?

pnauta,

By default, recovery model of msdb database is set to Simple and I feel there is no need to set it in Full Recovery model..
Kindly share your thoughts on why we require Full recovery model to solve this ( if I am wrong)..
0
brad2575Author Commented:
Thanks both of you, didn't quite solve the problem but did the best we could
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.