January 21, 2009 at 8:05 am
I have a job that runs as sa. I have users that have the ability to execute the job. This job updates a table and has two "auditing" columns. One for the name of the Windows user account executing the job and one for when the job was run.
I've tried each of the following: current_user, user_name(), session_user, system_user, user, SUSER_SNAME(), SUSER_NAME(). These either return dbo or the account running SQL Agent. According to BOL there is a job.user property, but I'm unsure how to utilize this from within the job.
I also know the information is stored in the msdb.dbo.sysjobhistory table's message column. I've used SQL Profiler but have not tracked down how this is retrieved.
How can I retrieve the Windows user account that invoked the job from within the job so this can be stored in the auditing column as the table is being updated?
January 21, 2009 at 8:23 am
If this user has his own computer and working on it, why not use HOSTNAME() on trigger, which will give you information from where u get the execution from
January 21, 2009 at 8:35 am
HOST_NAME() returns the name of the server the job is executing on.
January 21, 2009 at 12:04 pm
How are your users executing the job? If it is through a web interface or application you might be able to capture the user at that point. If they are using Management Studio you could try something like the following:
You can use tokens in job steps. These allow you to capture certain attributes at run time.
http://msdn.microsoft.com/en-us/library/ms175575.aspx
The first step in your job could use the tokens JOBID plus STRTDT and STRTTM (start date and time) and log those values to a reference table. Then a subsequent step could use that information to query sysjobs and sysjobhistory to return job info including the message column from sysjobhistory. For step_id = 1 the message column should contain something like: “Executed as user: Domain\User”. You could parse that and get the user.
That is a little rough but might give you some ideas.
January 21, 2009 at 12:16 pm
The job is executed from a vba macro. Perhaps I should capture the login here and update the table, but was hoping to have it done in one update statement in the job.
I had thought about doing what you suggested regarding parsing the username out of the message in jobhistory, but I believe that is not available until after the job runs 🙁
It really seems like there should be a clean way of capturing who executed the job from within the job.
January 21, 2009 at 12:37 pm
Hi, You can add one more step as the last step in your Job. The step will call Another another job (your auditing job).
In your auditing job, u can use the following scripts to update your audit table:
DECLARE @JobInvoker varchar(20),
@ExecTime varchar(20)
SELECT TOP 1 @jobInvoker=SUBSTRING(ltrim(rtrim(jh.message)),
CHARINDEX('was invoked by user', ltrim(rtrim(jh.message))) + LEN('was invoked by user')+1
, CHARINDEX('. The last step',ltrim(rtrim(jh.message)))
-CHARINDEX('was invoked by user', ltrim(rtrim(jh.message)))- LEN('was invoked by user')-1)
,@execTime= LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2)
+ ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2)
+ ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2)
+ ', '+ SUBSTRING(CAST(jh.run_date AS CHAR(8)),5,2)
+ '/' + RIGHT(CAST(jh.run_date AS CHAR(8)),2)
+ '/' + LEFT(CAST(jh.run_date AS CHAR(8)),4)
FROM msdb.dbo.sysjobhistory jh
JOIN msdb.dbo.sysjobs j
ON j.job_id=jh.job_id
WHERE jh.step_id=0
AND j.name like '%YOUJOBNAMEHERE%'
ORDER BY jh.instance_id desc
--PRINT @jobinvoker;
--PRINT @ExecTime
INSERT INTO MyJobAuditTable
(ExecuterName, RunTime)
Values (@Jobinvoker, @execTime)
January 21, 2009 at 12:52 pm
Did you actually try that by calling a second job from the first or did you just execute the query after executing a job? I was looking at doing the same thing with a slightly different query, but my theory is that the first job is still running as the second job is executed, therefore the history for the first job has not been written. If you do return a userid I believe it would in fact be the user that previously executed the job and not the user currently executing the job.
But I will check it out further after I frustrate myself with a for-loop issue in ssis. Unless of course someone else wants to test the theory first 😀
January 21, 2009 at 1:16 pm
You might be best off pushing the logging into the VBA macro. The macro could call a stored proc that logs user and time then starts the job via sp_start_job.
January 21, 2009 at 1:30 pm
Did you actually try that by calling a second job from the first or did you just execute the query after executing a job?
Calling a second job (auditing job) from the first( Audited Job) at the LAST step of the first. The second job ONLY gets to run after the first is done. So the query should capture the Latest run of your job. If you are not so sure, put some delay in front of the query in your second job.
WAITFOR DELAY '00:00:02'
With the above method, if the user comments out the last step, your auditing job will not get triggered.
Another way is: design your audit table to include the instance_id of every job run, set up an auditing job to poll the jobhistory and insert the info to your audit table for runs with bigger instance_id for the audited job.
This will ensure the auditing job keeps running and get the complete records.
January 21, 2009 at 2:46 pm
Thanks I will look into that. I have also considered eliminating the job and using a stored proc. We probably have no good reason this process needs to be set up as a job. This (using SP) might be the simplest and cleanest solution from what I'm seeing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply