Get job that ran SQL query on UPDATE trigger

Question

I am trying to create an audit trail for actions that are performed within a web application, SQL server agent jobs and manually run queries to the database. I am trying to use triggers to catch updates, inserts and deletes on certain tables.

In the whole this process is working. Example, user performs update in web application and the trigger writes the updated data to an audit trail table I have defined, including the username of the person who performed the action. This works fine from a web application or manual query perspective, but we also have dozens of SQL Server Agent Jobs that I would like to capture which one ran specific queries. Each of the agent jobs are ran with the same username. This works fine also and inputs the username correctly into the table but I can't find which job calls this query.

My current "solution" was to find which jobs are currently running at the time of the trigger, as one of them must be the correct one. Using:

CREATE TABLE #xp_results 

    ( 
    job_id                UNIQUEIDENTIFIER NOT NULL,   
    last_run_date         INT              NOT NULL,   
    last_run_time         INT              NOT NULL,   
    next_run_date         INT              NOT NULL,   
    next_run_time         INT              NOT NULL,   
    next_run_schedule_id  INT              NOT NULL,   
    requested_to_run      INT              NOT NULL, -- BOOL   
    request_source        INT              NOT NULL,   
    request_source_id     sysname          COLLATE database_default NULL,   
    running               INT              NOT NULL, -- BOOL   
    current_step          INT              NOT NULL,   
    current_retry_attempt INT              NOT NULL,   
    job_state             INT              NOT NULL
    )   

INSERT INTO  #xp_results  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'  
SELECT @runningJobs = STUFF((SELECT ',' + j.name 
                FROM #xp_results r
                INNER JOIN msdb..sysjobs j ON r.job_id = j.job_id
                WHERE running = 1
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

DROP TABLE #xp_results

I ran a specific job to test and it seems to work, in that any OTHER job which is running will be listed in @runningJobs, but it doesn't record the job that runs it. I assume that by the time the trigger runs the job has finished.

Is there a way I can find out what job calls the query that kicks off the trigger?

EDIT: I tried changing the SELECT query above to get any job that ran within the past 2 mins or is currently running. The SQL query is now:

SELECT @runningJobs = STUFF((SELECT ',' + j.name 
            FROM #xp_results r
            INNER JOIN msdb..sysjobs j ON r.job_id = j.job_id
            WHERE (last_run_date = CAST(REPLACE(LEFT(CONVERT(VARCHAR, getdate(), 120), 10), '-', '') AS INT)
            AND last_run_time > CAST(REPLACE(LEFT(CONVERT(VARCHAR,getdate(),108), 8), ':', '') AS INT) - 200)
            OR running = 1
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

When I run a job, then run the above query while the job is running, the correct jobs are returned. But when the SSIS package is run, either via the SQL Server Agent job or manually ran in SSIS, the @runningJobs is not populated and just returns NULL.

So I am now thinking it is a problem with permissions of SSIS and master.dbo.xp_sqlagent_enum_jobs. Any other ideas?

EDIT #2: Actually don't think it is a permissions error. There is an INSERT statement below this code, if it IS a permissions error the INSERT statement does not run and therefore the audit line does not get added to the database. So, as there IS a line added to the database, just not with the runningJobs field populated. Strange times.

EDIT #3: I just want to clarify, I am searching for a solution which DOES NOT require me to go into each job and change anything. There are too many jobs to make this a feasible solution.

Answer 1

WORKING CODE IS IN FIRST EDIT - (anothershrubery)

Use the app_name() function http://msdn.microsoft.com/en-us/library/ms189770.aspx in your audit trigger to get the name of the app running the query.

For SQL Agent jobs, app_name includes the job step id in the app name (if a T-SQL step). We do this in our audit triggers and works great. An example of the app_name() results when running from within an audit trigger:

SQLAgent - TSQL JobStep (Job 0x96EB56A24786964889AB504D9A920D30 : Step 1)

This job can be looked up via the job_id column in msdb.dbo.sysjobs_view.

Since SSIS packages initiate the SQL connection outside of the SQL Agent job engine, those connections will have their own application name, and you need to set the application name within the connection strings of the SSIS packages. In SSIS packages, Web apps, WinForms, or any client that connects to SQL Server, you can set the value that is returned by the app_name function by using this in your connection string :

"Application Name=MyAppNameGoesHere;" 

http://www.connectionstrings.com/use-application-name-sql-server/

If the "Application Name" is not set within a .NET connection string, then the default value when using the System.Data.SqlClient.SqlConnection is ".Net SqlClient Data Provider".

Some other fields that are commonly used for auditing:

• HOST_NAME(): http://technet.microsoft.com/en-us/library/ms178598.aspx Returns the name of the client computer that is connecting. This is helpful if you have an intranet app.
• CONNECTIONPROPERTY('local_net_address'): For getting the client IP address.
• CONTEXT_INFO(): http://technet.microsoft.com/en-us/library/ms187768.aspx You can use this to store information for the duration of the connection/session. Context_Info is a binary 128 byte field, so you might need to do conversions to/from strings when using it.

Here are SQL helper methods for setting/getting context info:

CREATE PROC dbo.usp_ContextInfo_SET
    @val varchar(128)
as
begin
    set nocount on;
    DECLARE @c varbinary(128);
    SET @c=cast(@val as varbinary(128));
    SET CONTEXT_INFO @c;
end
GO

CREATE FUNCTION [dbo].[ufn_ContextInfo_Get] ()
RETURNS varchar(128)
AS
BEGIN
    --context_info is binary data type, so will pad any values will CHAR(0) to the end of 128 bytes, so need to replace these with empty string.
    RETURN REPLACE(CAST(CONTEXT_INFO() AS varchar(128)), CHAR(0), '')
END

EDIT:

The app_name() is the preferred way to get the application that is involved in the query, however since you do not want to update any of the SSIS packages, then here is an updated query to get currently executing jobs using the following documented SQL Agent tables. You may have to adjust the GRANTs for SELECT in the msdb database for these tables in order for the query to succeed, or create a view using this query, and adjust the grants for that view.

• msdb.dbo.sysjobactivity http://msdn.microsoft.com/en-us/library/ms190484.aspx
• msdb.dbo.syssessions http://msdn.microsoft.com/en-us/library/ms175016.aspx
• msdb.dbo.sysjobs http://msdn.microsoft.com/en-us/library/ms189817.aspx
• msdb.dbo.sysjobhistory http://msdn.microsoft.com/en-us/library/ms174997.aspx

Query:

;with cteSessions as
(
    --each time that SQL Agent is started, a new record is added to this table.
    --The most recent session is the current session, and prior sessions can be used 
    --to identify the job state at the time that SQL Agent is restarted or stopped unexpectedly
    select top 1 s.session_id
    from msdb.dbo.syssessions s
    order by s.agent_start_date desc
)
SELECT runningJobs =
    STUFF(
    (   SELECT N', [' + j.name + N']'
        FROM msdb.dbo.sysjobactivity a
            inner join cteSessions s on s.session_id = a.session_id
            inner join msdb.dbo.sysjobs j on a.job_id = j.job_id
            left join msdb.dbo.sysjobhistory h2 on h2.instance_id = a.job_history_id
        WHERE 
            --currently executing jobs:
            h2.instance_id is null
            AND a.start_execution_date is not null
            AND a.stop_execution_date is null
        ORDER BY j.name
        FOR XML PATH(''), ROOT('root'), TYPE
    ).query('root').value('.', 'nvarchar(max)') --convert the xml to nvarchar(max)
    , 1, 2, '') -- replace the leading comma and space with empty string.
;

EDIT #2:

Also if you are on SQL 2012 or higher, then checkout the SSISDB.catalog.executions view http://msdn.microsoft.com/en-us/library/ff878089(v=sql.110).aspx to get the list of currently running SSIS packages, regardless of if they were started from within a scheduled job. I have not seen an equivalent view in SQL Server versions prior to 2012.

Answer 2

I would add an extra column to your table e.g. Update_Source, and get all the source apps (including SSIS) to set it when they update the table.

You could use the USER as a DEFAULT for that column to minimize the changes needed.

Answer 3

You could try using CONTEXT_INFO

Try adding a T-SQL step with SET CONTEXT_INFO 'A Job' in to your job

Then try reading that in your trigger using sys.dm_exec_sessions

I'm curious to see if it works - please post your findings.

http://msdn.microsoft.com/en-us/library/ms187768(v=sql.105).aspx