监测数据库的结构变化

有时候，一个项目里有很多人都有权限修改数据库的结构，就会造成，一个人更改了表结构，而其他开发者不知道的情况。为了记录那些人对哪些结构进行了修改。下面有一段脚本可以记录修改活动。

首先创建一个表，用来记录修改的活动。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ddl_log](
   [log_id] [int] IDENTITY(1,1) NOT NULL,
   [post_time] [datetime] NULL,
   [server_name] [varchar](100) NULL,
   [database_name] [varchar](100) NULL,
   [login_name] [varchar](100) NULL,
   [target_object_name] [varchar](100) NULL,
   [event_type] [varchar](100) NULL,
   [command_text] [varchar](8000) NULL,
   [client_host_name] [varchar](128) NULL,
   [client_ip] [varchar](20) NULL,
   [client_mac] [varchar](20) NULL,
   [client_program_name] [nvarchar](128) NULL,
   [event_instance] [xml] NULL,
PRIMARY KEY CLUSTERED
(
   [log_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

接着写一个触发器，将对数据库结构的修改行为记录紧上面的表中。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create trigger [ddl_trigger_database]
    on database
    for ddl_database_level_events
as
    declare @data xml,
    @post_time datetime,           --创建时间
    @server_name varchar(100),         --服务器名
    @databasename varchar(100),        --数据库名称
    @login_name varchar(100),          --登录用户名称
    @target_object_name varchar(100), --触发对象名称
    @event_type varchar(100),          --事件类型
    @command_text varchar(8000),       --执行代码
    @client_host_name varchar(128),    --工作站名称
    @client_ip varchar(20),            --客户端ip地址
    @client_mac varchar(20),        --客户端mac地址
    @client_program_name nvarchar(128) --客户端应用程序

    set @data=EVENTDATA()

    --path 区分大小写
    set @databasename=@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)')
    set @target_object_name=@data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
    if lower(@target_object_name)='ddl_log' and @data.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(100)')='TABLE'

    begin

       raiserror('deleted or alter table ddl_log fail.', 16, -1)
       rollback transaction
       return
    end

    set @post_time=@data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
    set @server_name=@data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
    set @login_name=@data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
    set @event_type=@data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(100)')
    set @command_text=@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(8000)')
    set @command_text=ltrim(rtrim(replace(replace(@command_text,char(10),' '),char(13),' '))) --换行替换为空格
    select @client_ip = client_net_address from sys.dm_exec_connections where session_id = @@spid
    select @client_program_name = program_name,@client_host_name=host_name from sys.dm_exec_sessions where session_id = @@spid
    select @client_mac = net_address from master.dbo.sysprocesses where spid = @@spid
    insert into [ddl_log]
          ([post_time]
          ,[server_name]
          ,[database_name]
          ,[login_name]
          ,[target_object_name]
          ,[event_type]
          ,[command_text]
          ,[client_host_name]
          ,[client_ip]
          ,[client_mac]
          ,[client_program_name]
          ,[event_instance])
    values
       (@post_time
       ,@server_name
       ,@databasename
       ,@login_name
       ,@target_object_name
       ,@event_type
       ,@command_text
       ,@client_host_name
       ,@client_ip
       ,@client_mac
       ,@client_program_name
       ,@data)

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [ddl_trigger_database] ON DATABASE
GO

ENABLE TRIGGER [ddl_trigger_database] ON DATABASE
GO

这样通过查询第一步中的表，就可查看，哪些用户对数据库执行了哪些操作了。

哈哈。