事态严重,请高手帮忙. 监控数据库设计修改.

Question

    最近,我们的数据库频繁被修改. 急需查找修改来自哪里. 我现在想监控数据库的表结构、触发器、视图、存储过程等，上一次或将来被修改时的时间，修改来自于哪台电脑或哪个用户等。
    这些有没有办法被监控？也许查看日志文档可以，可我不知道怎么查看。
    谢谢关注。
    

Answer 1

建议使用一个DDL触发器跟踪，如下：
--创建跟踪表，记录表更新情况
IF EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME LIKE 'TB_LOG')
DROP TABLE TB_LOG
GO
CREATE TABLE TB_LOG
(
ID INT IDENTITY(1,1) PRIMARY KEY,
SPID smallint,
LOGIN_TIME datetime,
HOSTNAME nchar(128),
PROGRAM_NAME nchar(128),
NT_DOMAIN nchar(128),
NT_USERNAME nchar(128),
NET_ADDRESS nchar(12),
IP_ADDRESS varchar(20),
NET_LIBRARY nchar(12),
LOGINAME nchar(128),
TableName varchar(100),
CHANGE_TIME datetime,
CHANGE_MODE varchar(20),
CHANGE_Old XML,
CHANGE_New XML 
)
GO
IF EXISTS(SELECT NAME FROM SYS.TRIGGERS WHERE NAME LIKE 'Track')
DROP TRIGGER Track
GO/*
功能：创建触发器,记录更改情况
日期：2008-09-10
作者：贾桂军注意:
1.更改触发器的触发表名
2.更改@TName的默认值为当前表名
3.更改触发器中的For XML RAW('ID')字段名
4.OK说明：如果要记录IP地址则必须打开xp_cmdshell功能，否则请注释掉触发器中的"获取IP地址"部分代码
开启xp_cmdshell功能可能存在安全隐患，也可能导致效率降低
*/
CREATE TRIGGER Track
ON T1
AFTER INSERT,DELETE,UPDATE
AS
SET NOCOUNT ON
--判断模式(select,delete,update)
DECLARE @MODE varchar(20),@ConI Int,@ConD Int,@IP varchar(20),@TName varchar(100)
SELECT @ConI=0,@ConD=0,@IP='',@TName='T1'
SELECT @ConI=Count(1) FROM INSERTED
SELECT @ConD=Count(1) FROM DELETED
IF @ConI>0 AND @ConD>0
SET @MODE='UPDATE'
ELSE IF @ConI>0 AND @ConD<1
SET @MODE='INSERT'
ELSE IF @ConI<1 AND @ConD>0
SET @MODE='DELETE'
-- /*
--获取IP地址
DECLARE @TIP TABLE(ID Int Identity(1,1),IP varchar(200))  
DECLARE @S varchar(500)
SET @S='ping  '+host_name()+' -a -n 1 -l 1'  
INSERT @TIP(IP) exec xp_cmdshell @S
select @IP=STUFF(LEFT(IP,CharIndex(']',IP)-1),1,CharIndex('[',IP),'')  
FROM @TIP where ID=2
-- */ --插入相关信息
INSERT TB_LOG(SPID, LOGIN_TIME, HOSTNAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, 
NET_ADDRESS,IP_ADDRESS, NET_LIBRARY, LOGINAME,TableName, CHANGE_TIME, CHANGE_MODE, 
CHANGE_Old, CHANGE_New)
SELECT SPID,LOGIN_TIME,HOSTNAME,PROGRAM_NAME,NT_DOMAIN,NT_USERNAME,
NET_ADDRESS,@IP,NET_LIBRARY,LOGINAME,@TName,GETDATE(),@MODE,
(SELECT * FROM DELETED FOR XML RAW('ID')),(SELECT * FROM INSERTED FOR XML RAW('ID'))
FROM master..sysprocesses WHERE SPID=@@SPID
SET NOCOUNT OFF
GO

Answer 2

CREATE TRIGGER Track
    ON T1
AFTER INSERT,DELETE,UPDATE
这里是表名，没办法对整个数据库的表修改进行记录，每个表加一个触发器太麻烦了。另外，这类触发器会和业务型触发器弄混，不方便维护。还有对存储过程、视图的修改如何记录？

Answer 3

对.还有如5楼所述的问题.
请问一楼,Happy_Stone, 用事件控测器查看,具体怎么操作?

Answer 4

用logexplorer吧，可以监测库中任何表单更改。

Answer 5

对于 2005 或更高的版本, 楼主可以建立一个服务器级别的 DDL 触发器来记录结构变更事件, 需要的时候, 查询记录的表就知道结构变更的相关信息了(变更信息是以 xml 格式存储的, 要根据具体需要进行解析)具体的实现参考下面的
USE master;
GO-- 记录更改信息的表
CREATE TABLE dbo.tb_DDL_change(
InDate datetime
DEFAULT GETDATE(),
event_data xml;
);
GO-- 记录事件的触发器
CREATE TRIGGER TR_DDL_change
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT dbo.tb_DDL_change(
event_data)
VALUES(
EVENTDATA());
GO

Answer 6

代码有点小错, 调整一下
USE master;
GO-- 记录更改信息的表
CREATE TABLE dbo.tb_DDL_change(
InDate datetime
DEFAULT GETDATE(),
event_data xml
);
GO-- 记录事件的触发器
CREATE TRIGGER TR_DDL_change
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT dbo.tb_DDL_change(
event_data)
VALUES(
EVENTDATA());
GO
/* -- 删除示例
DROP TRIGGER TR_DDL_change
ON ALL SERVER;DROP TABLE dbo.tb_DDL_change
--*/

Answer 7

对于 2000 及之前的版本, 你可以考虑用事件探查器跟踪, 选择下面三个事件就可以了:
Objects:
  -- Object: Altered
  -- Object: Created
  -- Object: Deleted另外, 在 sql 2005 及 2008 中, 如果启用了默认跟踪(默认情况下是启用的), 也会自动跟踪上述三个事件, 并且可以通过实例(或数据库)上的右键菜单--报表--标准报表--架构变更历史记录来查看跟踪到的信息(当然, 跟踪文件过大时, 会自动截断, 所以随着实例的使用, 太掉的回构变更信息也会看不到)

Answer 8

需要补充的是, 对于事件探查器跟踪的方法, Object 变更的三个事件没有提供变更的详细操作语句, 而DDL触发器中是有的
如果要在事件探查器跟踪中找到这个语句, 还需要把子T-SQL 和存储过程的 Completed 事件选上(副作用就是会包含很多 DDL 之外的事件, 需要你自己在分析的时候去过滤)

Answer 9

-- 记录事件的触发器
create trigger tr_ddl_change
on all server
for ddl_database_level_events
as
insert dbo.tb_ddl_change(
    event_data)
values(
    eventdata()
);
go我用的是2005，创建出错。
The specified event type(s) is/are not valid on the specified target object.

Answer 10

从错误提示看, 应该是你的表的列不是 xml 类型导致的