?
Solved

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

Posted on 2010-04-01
20
Medium Priority
?
416 Views
Last Modified: 2013-11-10
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?
0
Comment
Question by:brad2575
  • 7
  • 7
  • 5
  • +1
20 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1000 total points
ID: 29347465
>>  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
 
LVL 16

Author Comment

by:brad2575
ID: 29348038
The job I am looking for does not have a schedule on it so it would have to have been started manually.
0
 
LVL 6

Assisted Solution

by:pnauta
pnauta earned 1000 total points
ID: 29352607
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29352700
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
 
LVL 16

Author Comment

by:brad2575
ID: 29353494
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
 
LVL 6

Expert Comment

by:pnauta
ID: 29353599
Maybe have a look at the SQLAGENT.OUT file.
0
 
LVL 16

Author Comment

by:brad2575
ID: 29356592
I checked that and it is the same thing that is in the event viewer for the SQL Agent.

Any other suggestions?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29399845
>> 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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 29789992
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
 
LVL 16

Author Comment

by:brad2575
ID: 29901430
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29905741
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
 
LVL 16

Author Comment

by:brad2575
ID: 29909296
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29910022
>> 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
 
LVL 6

Expert Comment

by:pnauta
ID: 29911374
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
 
LVL 16

Author Comment

by:brad2575
ID: 29912942
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
 
LVL 6

Expert Comment

by:pnauta
ID: 29914614
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29916671
Try running profiler across that table and see whether any inserts or deletes are happening on that table.
0
 
LVL 6

Expert Comment

by:pnauta
ID: 30762391
So, how are you doing in your own CSI episode :-) ?  Was the msdb database in full recovery mode?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 30775039
>> 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
 
LVL 16

Author Closing Comment

by:brad2575
ID: 31709905
Thanks both of you, didn't quite solve the problem but did the best we could
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question