监控数据库DDL操作日志

背景

为了监控好生产环境下各个数据库服务器上DDL操作日志，便于运维工程师管控好风险，我们有必要关注当前实例下的所有的DDL操作以及对应的IP和hostname。

测试环境

Microsoft SQL Server 2012 - 11.0.2218.0 (X64) 

Jun 12 2012 13:05:25 

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

操作步骤

第一步.在监控库中新建DDL监控表用来存放DDL监控日志记录

--新建监控库，如果已存在该数据库，可以不执行
USE master;
IF DB_ID('azure_monitor') IS NOT NULL
    DROP DATABASE azure_monitor;

CREATE DATABASE azure_monitor
ON
--请根据实际情况选择监控库的存放路径
(   NAME = azure_monitor,
    FILENAME = 'd:azure_monitor.mdf',
    FILEGROWTH = 50MB
)
LOG ON
(   NAME = azure_monitor_log,
    FILENAME = 'd:azure_monitore_log.ldf',
    FILEGROWTH = 50MB
);

USE master;
ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;

 

USE [azure_monitor];


CREATE TABLE [dbo].[monitor_DatabaseLog]
(
    [DatabaseLogID] [INT] IDENTITY(1, 1) NOT NULL,
    [PostTime] [DATETIME] NOT NULL,
    [DatabaseUser] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [Event] [sysname] NOT NULL,
    [databasename] [sysname] NULL,
    [Schema] [sysname] NULL,
    [Object] [sysname] NULL,
    [TSQL] [NVARCHAR](MAX) NOT NULL,
    [XmlEvent] [XML] NOT NULL,
    [IP] [NVARCHAR](32) NULL,
    [hostname] [NVARCHAR](100) NULL,
    CONSTRAINT [PK_DatabaseLog_DatabaseLogID]
        PRIMARY KEY NONCLUSTERED ([DatabaseLogID] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                 ALLOW_PAGE_LOCKS = ON
             ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

GO

第二步.新建实例级别的触发器

CREATE TRIGGER [ddlDatabaseTriggerLog]
ON ALL SERVER
WITH EXECUTE AS 'sa'  --根据实际情况选择
FOR DDL_DATABASE_LEVEL_EVENTS, CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE, CREATE_LOGIN
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @LoginName sysname;
    DECLARE @databasename sysname;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @ip VARCHAR(32) =
            (
                SELECT client_net_address
                FROM sys.dm_exec_connections
                WHERE session_id = @@SPID
            );

    DECLARE @hostname NVARCHAR(100) = HOST_NAME();

    SET @data = EVENTDATA();
    SET @LoginName
        = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
    SET @databasename
        = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
    SET @eventType
        = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @databasename + '.' + @schema + '.'
              + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @databasename + '.' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(NVARCHAR(MAX), @data);
    ---检查写入的日志记录对应的库名是否正确
    INSERT [azure_monitor].[dbo].[monitor_DatabaseLog]
    (   [PostTime],
        [DatabaseUser],
        [LoginName],
        [Event],
        [databasename],
        [Schema],
        [Object],
        [TSQL],
        [XmlEvent],
        [ip],
        [hostname]
    )
    VALUES
    (   GETDATE(),
        CONVERT(sysname, CURRENT_USER),
        CONVERT(sysname, @LoginName),
        @eventType,
        CONVERT(sysname, @databasename),
        CONVERT(sysname, @schema),
        CONVERT(sysname, @object),
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @data,
        @ip,
        @hostname
    );
END;

监控效果

后记

1. 所有的人员登陆都已提前开设好各自的登陆用户；
2. 严格隔离区分不同的人员之间操作权限；

 

---清空记录存储过程
CREATE PROCEDURE [dbo].[usp_PurgeOldData_databaselog] ( @PurgeWaits SMALLINT )
AS
    BEGIN;      
        IF @PurgeWaits IS NULL
            BEGIN;
                RAISERROR(N'Input parameters cannot be NULL', 16, 1);
                RETURN;
            END;
        DELETE  FROM [dbo].[monitor_DatabaseLog]
        WHERE   [PostTime] < GETDATE() - @PurgeWaits;
    END;

GO

参考

create trigger