前言：有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如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.',@level2name=N'PostTime'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The user who implemented the DDL change.',@level2name=N'DatabaseUser'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The login which implemented the DDL change.',@level2name=N'LoginName'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The client machine on which implemented the DDL change.',@level2name=N'ClientHost'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The type of DDL statement that was executed.',@level2name=N'Event'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The schema to which the changed object belongs.',@level2name=N'Schema'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The object that was changed by the DDL statment.',@level2name=N'Object'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The exact Transact-sql statement that was executed.',@level2name=N'Tsql'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'The raw XML data generated by database trigger.',@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.',@level1name=N'DatabaseLog'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description',@value=N'Primary key (nonclustered) constraint',@level2type=N'CONSTRAINT',@level2name=N'PK_DatabaseLog_DatabaseLogID'
GO

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

代码如下:

USE MyAssistant;
GOCREATE 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)'),HOST_NAME()),
@eventType,@schema),@object),
@data.value('(/EVENT_INSTANCE/TsqlCommand)[1]',
@data
); SET @tableHTML =
N'

DDL Event

' +
N' N'Post Time' +
N'

User

ClientHost

Tsql

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' @profile_name = 'DataBase_DDL_Event',
@recipients='***@***.com',
@subject = 'DDL Event - DataBase MyAssistant',
@body = @tableHTML,
@body_format = 'HTML' ;
END;
GO

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

clipboard