Link to home
Start Free TrialLog in
Avatar of brad2575
brad2575Flag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brad2575

ASKER

The job I am looking for does not have a schedule on it so it would have to have been started manually.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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).
Maybe have a look at the SQLAGENT.OUT file.
I checked that and it is the same thing that is in the event viewer for the SQL Agent.

Any other suggestions?
>> 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..
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
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.
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..
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.
>> 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..
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.
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.
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.
Try running profiler across that table and see whether any inserts or deletes are happening on that table.
So, how are you doing in your own CSI episode :-) ?  Was the msdb database in full recovery mode?
>> 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)..
Thanks both of you, didn't quite solve the problem but did the best we could