前言： 有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如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操作(如下图二所示)，当然邮件的样式、排版有兴趣的可以去美化一下。