Получить работу, которая запустила SQL-запрос по триггеру UPDATE

Question

Я пытаюсь создать контрольный журнал для действий, выполняемых в веб-приложении, заданиях агента сервера SQL и вручную выполнять запросы к базе данных. Я пытаюсь использовать триггеры, чтобы ловить обновления, вставки и удаления в определенных таблицах.

В целом этот процесс работает. Например, пользователь выполняет обновление в веб-приложении, и триггер записывает обновленные данные в определенную мной таблицу контрольного журнала, включая имя пользователя, который выполнил действие. Это прекрасно работает с точки зрения веб-приложения или запроса вручную, но у нас также есть десятки заданий агента SQL Server, которые я хотел бы получить, какие из них выполняли определенные запросы. Каждое из заданий агента выполняется с тем же именем пользователя. Это также прекрасно работает и правильно вводит имя пользователя в таблицу, но я не могу найти, какая работа вызывает этот запрос.

Мое текущее "решение" состояло в том, чтобы найти, какие задания выполняются в данный момент во время триггера, поскольку одно из них должно быть правильным. С помощью:

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

Я запустил определенное задание для проверки, и, похоже, оно работает, так как любое другое задание, которое выполняется, будет перечислено в @runningJobs, но он не записывает работу, которая его запускает. Я предполагаю, что к моменту запуска триггера работа завершена.

Есть ли способ узнать, какая работа вызывает запрос, который запускает триггер?

РЕДАКТИРОВАТЬ: я пытался изменить SELECT запрос выше, чтобы получить любую работу, которая выполнялась в течение последних 2 минут или в настоящее время выполняется. Запрос SQL теперь:

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, '')

Когда я запускаю задание, а затем выполняю указанный выше запрос во время выполнения задания, возвращаются правильные задания. Но когда пакет SSIS запущен, либо через задание агента SQL Server, либо вручную запущен в SSIS, @runningJobs не заселен и просто возвращается NULL,

Так что я теперь думаю, что это проблема с разрешениями SSIS и master.dbo.xp_sqlagent_enum_jobs, Есть другие идеи?

РЕДАКТИРОВАТЬ # 2: На самом деле не думаю, что это ошибка разрешений. Есть INSERT заявление под этим кодом, если это ошибка разрешений INSERT оператор не выполняется и, следовательно, строка аудита не добавляется в базу данных. Таким образом, поскольку есть строка, добавленная в базу данных, но не с runningJobs поле заселено. Странные времена.

РЕДАКТИРОВАТЬ # 3: Я просто хочу уточнить, я ищу решение, которое не требует, чтобы я пошел на каждую работу и что-то изменить. Слишком много рабочих мест, чтобы сделать это возможным решением.

Answer 1

РАБОЧИЙ КОД В ПЕРВОМ РЕДАКЦИИ - (еще одинкряк)

Использовать app_name() Функция http://msdn.microsoft.com/en-us/library/ms189770.aspx в триггере аудита, чтобы получить имя приложения, выполняющего запрос.

Для заданий агента SQL имя_приложения содержит идентификатор шага задания в имени приложения (если это шаг T-SQL). Мы делаем это в наших триггерах аудита и отлично работает. Примером app_name() результаты при запуске из триггера аудита:

SQL Agent - TSQL JobStep (задание 0x96EB56A24786964889AB504D9A920D30: шаг 1)

Эту работу можно посмотреть через job_id колонка в msdb.dbo.sysjobs_view,

Поскольку пакеты служб SSIS инициируют подключение SQL вне механизма заданий агента SQL, эти подключения будут иметь свое собственное имя приложения, и вам необходимо указать имя приложения в строках подключения пакетов служб SSIS. В пакетах служб SSIS, веб-приложениях, WinForms или любом клиенте, который подключается к SQL Server, вы можете установить значение, возвращаемое функцией app_name, используя это в строке подключения:

"Application Name=MyAppNameGoesHere;" 

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

Если "Имя приложения" не задано в строке подключения.NET, тогда значение по умолчанию при использовании System.Data.SqlClient.SqlConnection такое "поставщик данных.Net SqlClient".

Некоторые другие поля, которые обычно используются для аудита:

• HOST_NAME (): http://technet.microsoft.com/en-us/library/ms178598.aspx Возвращает имя подключаемого клиентского компьютера. Это полезно, если у вас есть приложение для интранета.
• CONNECTIONPROPERTY ('local_net_address'): для получения IP-адреса клиента.
• CONTEXT_INFO (): http://technet.microsoft.com/en-us/library/ms187768.aspx Вы можете использовать это для хранения информации в течение всего времени соединения / сеанса. Context_Info - это двоичное 128-байтовое поле, поэтому вам может потребоваться выполнить преобразование в / из строк при его использовании.

Вот вспомогательные методы SQL для установки / получения контекстной информации:

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

РЕДАКТИРОВАТЬ:

App_name() - это предпочтительный способ получить приложение, которое участвует в запросе, однако, поскольку вы не хотите обновлять какой-либо из пакетов служб SSIS, здесь приведен обновленный запрос для получения выполняемых в данный момент заданий с использованием следующего документированного агента SQL столы. Возможно, вам придется настроить GRANT для SELECT в базе данных msdb для этих таблиц, чтобы запрос был выполнен успешно, или создать представление с использованием этого запроса и настроить разрешения для этого представления.

• 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

Запрос:

;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.
;

РЕДАКТИРОВАТЬ № 2:

Кроме того, если вы используете SQL 2012 или более поздней версии, SSISDB.catalog.executions просмотрите http://msdn.microsoft.com/en-us/library/ff878089%28v=sql.110%29.aspx чтобы получить список запущенных в настоящее время пакетов служб SSIS, независимо от того, были ли они запущены из запланированного задания. Я не видел аналогичного представления в версиях SQL Server до 2012 года.

Answer 2

Я хотел бы добавить дополнительный столбец к вашей таблице, например Update_Source, и получить все исходные приложения (включая SSIS), чтобы установить его при обновлении таблицы.

Вы можете использовать ПОЛЬЗОВАТЕЛЬ в качестве ПО УМОЛЧАНИЮ для этого столбца, чтобы минимизировать необходимые изменения.

Answer 3

Вы можете попробовать использовать CONTEXT_INFO

Попробуйте добавить шаг T-SQL с SET CONTEXT_INFO 'A Job' в вашей работе

Затем попробуйте прочитать это в вашем триггере, используя sys.dm_exec_sessions

Мне любопытно посмотреть, работает ли это - пожалуйста, опубликуйте свои выводы.

http://msdn.microsoft.com/en-us/library/ms187768%28v=sql.105%29.aspx