MSSQL监控数据库的DDL操作（创建，修改，删除存储过程，创建，修改，删除表等）

前言：有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如DDL操作权限（创建，修改，删除存储过程，创建，修改，删除表等），账户多了，管理起来就会相当麻烦，容易产生混乱，如果数据库管理员不监控数据库架构变更的话，就不知道谁对数据库架构做了啥改动（此处改动仅仅只DDL操作），尤其有时候，有些开发人员可能不按规章制度办事，绕过或忘了通知发布人员或DBA，直接去生产机做一些DDL操作，那么我们就需要对数据库架构某些更改的事件进行监控，如果能够监控并留下证据，这样既可以让DBA或相关管理人员知晓这些变更，有效管理数据库，也可以避免出现问题，出现扯皮现象，最后DBA成了背黑锅的。

下面就是一个解决上述问题的方案，我们通过创建一个表DatabaseLog和DDL触发器来解决问题，首先在msdb数据库里面新建一个表DatabaseLog，用来保存DDL触发器获取的信息。其中DDL触发器主要通过EVENTDATA（）函数返回有关服务器或数据库事件的信息。

复制代码代码如下:
USE msdb;
GO
CREATE TABLE [dbo].[DatabaseLog]
（
    [DatabaseLogID]   [int]    IDENTITY（1,1） NOT NULL,
    [PostTime]        [datetime] NOT NULL,
    [DatabaseUser]    [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LoginName]       [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ClientHost]      [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Event]           [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Schema]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Object]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TSQL]            [nvarchar]（max） COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XmlEvent]        [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
（
    [DatabaseLogID] ASC
）WITH （PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF） ON [PRIMARY]
） ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"Primary key for DatabaseLog records." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"DatabaseLogID"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The date and time the DDL change occurred." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"PostTime"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The user who implemented the DDL change." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"DatabaseUser"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The login which implemented the DDL change." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"LoginName"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The client machine on which implemented the DDL change." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"ClientHost"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The type of DDL statement that was executed." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"Event"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The schema to which the changed object belongs." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"Schema"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The object that was changed by the DDL statment." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"Object"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The exact Transact-SQL statement that was executed." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"TSQL"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"The raw XML data generated by database trigger." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"COLUMN",@level2name=N"XmlEvent"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"Audit table tracking all DDL changes made to the database. Data is captured by the database trigger ddlDatabaseTriggerLog." , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog"
GO
EXEC sys.sp_addextendedproperty@name=N"MS_Description", @value=N"Primary key （nonclustered） constraint" , @level0type=N"SCHEMA",@level0name=N"dbo", @level1type=N"TABLE",@level1name=N"DatabaseLog", @level2type=N"CONSTRAINT",@level2name=N"PK_DatabaseLog_DatabaseLogID"
GO

例如，我要监控数据库MyAssistant的DDL操作，那么我们首先在“数据库邮件”里面创建一个配置名为“ DataBase_DDL_Event”的配置文件（profile name）,这个就不多讲了，不知道配置的，自己先练练手把，假如我需要让数据库把监控到DDL操作变动相信信息发送到我的邮箱 *****@***.com（用你自己的邮箱替代），那么只需要修改下面代码的邮箱和profile_name即可。

复制代码代码如下:
USE MyAssistant;
GO

CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @tableHTML NVARCHAR（MAX） ;

    SET @data = EVENTDATA（）;
    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 + " - " + @schema + "." + @object;
    ELSE
        PRINT " " + @eventType + " - " + @schema;

    IF @eventType IS NULL
        PRINT CONVERT（nvarchar（max）, @data）;

    INSERT [msdb].[dbo].[DatabaseLog]
        （
        [PostTime],
        [DatabaseUser],
        [LoginName],
        [ClientHost],
        [Event],
        [Schema],
        [Object],
        [TSQL],
        [XmlEvent]
        ）
    VALUES
        （
        GETDATE（）,
        CONVERT（sysname, CURRENT_USER）,
        @data.value（"（/EVENT_INSTANCE/LoginName）[1]", "nvarchar（max）"）,
        CONVERT（sysname, HOST_NAME（））,
        @eventType,
        CONVERT（sysname, @schema）,
        CONVERT（sysname, @object）,
        @data.value（"（/EVENT_INSTANCE/TSQLCommand）[1]", "nvarchar（max）"）,
        @data
        ）;

    SET @tableHTML =
    N"<H1>DDL Event</H1>" +
    N"<table border="0">" +
    N"<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th>" +
    N"<th>TSQL</th><th></tr>" +
    CAST（（ SELECT
    td = PostTime,       "",
    td = DatabaseUser, "",
    td = LoginName, "",
    td = ClientHost, "",
    td = TSQL, ""
    FROM msdb.dbo.DatabaseLog
    WHERE DatabaseLogID =（select max（DatabaseLogID） from msdb.dbo.DatabaseLog）
    FOR XML PATH（"tr"）, TYPE     ） AS NVARCHAR（MAX）） +     N"</table>" ;

    EXEC msdb.dbo.sp_send_dbmail
             @profile_name = "DataBase_DDL_Event",
        @recipients="***@***.com",
        @subject = "DDL Event - DataBase MyAssistant",
        @body = @tableHTML,
        @body_format = "HTML" ;
END;
GO

接下来我们来测试一下，假如一个用户Test登录数据库，一不小心删除了一个Test的表，如下图一所示，那么我将收到一封邮件，提示我用户Test在那台客户端主机执行了啥DDL操作（如下图二所示），当然邮件的样式、排版有兴趣的可以去美化一下。

clipboard

clipboard[1]