Início / dba / Perguntas / 120579
Accepted
Peter
Asked: 2015-11-11 01:28:33 +0800 CST

Ações de log do SQL Server Agent e alterações feitas em trabalhos

  • 772

Existe uma maneira de rastrear as alterações feitas nos trabalhos do SQL Server Agent?

Se você tivesse um motivo para acreditar que alguém está brincando com suas configurações de trabalho. Como você procederia para rastrear quem fez isso e de onde as alterações foram feitas no trabalho individual?

Estou pensando na linha de eventos estendidos.

sql-server sql-server-agent

1 respostas

  1. Best Answer

    Para simplificar, assumirei que você deseja rastrear sysjobs, sysjobsteps e sysjobschedules. Pode haver outras tabelas que você deseja monitorar.

    Opção 1: Auditoria SQL (requer Enterprise Edition)

    USE [master]
GO

-- Audit
CREATE SERVER AUDIT [jobs]
TO FILE 
(   FILEPATH = N'PathToSomeFolder'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e807469a-6c9d-43f1-af46-cf7b89ba898d'
)
ALTER SERVER AUDIT [jobs] WITH (STATE = ON)
GO

USE [msdb]
GO

CREATE DATABASE AUDIT SPECIFICATION [job_changes]
FOR SERVER AUDIT [jobs]
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobsteps] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobschedules] BY [public])
WITH (STATE = ON)
GO

    Os dados capturados pela auditoria aparecem assim: Auditoria SQL

    Opção 2: sessão de eventos estendidos

    -- Step 1: extract object_id for the following tables
SELECT object_id 
from sys.tables
WHERE name IN ('sysjobs','sysjobsteps','sysjobschedules');

-- Step 2: use those object_ids in the following session:
CREATE EVENT SESSION [capture_job_changes] ON SERVER
ADD EVENT sqlserver.lock_acquired (
    SET collect_database_name = (0)
        ,collect_resource_description = (1)
    ACTION(sqlserver.client_app_name, sqlserver.is_system, sqlserver.server_principal_name)
    WHERE (
        [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[equal_uint64]([database_id], (4))  -- msdb
        AND (
               [object_id] = 1125579048 -- sysjobs
            OR [object_id] = 1269579561 -- sysjobsteps
            OR [object_id] = 1477580302 -- sysjobschedules
        )
        AND (
               [mode] = (8) -- IX
            OR [mode] = (5) -- X
        )
    )
)
WITH (
     MAX_MEMORY = 20480 KB
    ,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = OFF
);
GO

-- Step 3: add a convenient target to the session (file target?)

    Os dados na sessão XE aparecem assim: sessão XE

    Em relação a esta segunda opção, escrevi um post no blog sobre um assunto semelhante ( tracking object use ) onde descrevo os detalhes da técnica. Basicamente, você pode considerar os bloqueios IX/X como atualizações nas tabelas subjacentes.

    Esta sessão captura o mínimo, mas você pode adicionar mais campos/ações a ela para capturar o texto sql ou o nome do computador ou o que fizer sentido para você.

    • 5

relate perguntas