监控数据库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