如何保存数据库级别修改的记录

1月 17th, 2011

前几天在论坛上发了一个帖子”SQL Server 2008如何查看表结构修改记录“,很感谢马上有人热心回复并给出建议,现在我把问题的原因和到最后的解决办法与大家分享,希望对大家有帮助.

问题:上午一上班就有用户打电话说系统出了问题,第一反应是马上去看系统是否有BUG,经过测试之后发现系统是完全没有问题的,但是原来能使用此页面的用户竟然都没有了权限,再次DEBUG系统时,发现从数据库的权限表里拿到的数据都是乱码,推测应该是有人修改了表的”排序规则”,使得中文部分都变成了问号,最郁闷的是查看日志之后也不知道是谁修改的,赶紧先把数据库恢复,之后分析原因主要有以下几点:

1.对数据库权限粒度划分不明确;

2.对数据库修改的日志没有记录;

解决办法:

1.创建数据库修改日志表(如何害怕不小心被删除,可以参考防止表被删除的触发器)

  1. USE [QSBN]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. — 创建数据库修改日志表
  8. CREATE TABLE dbo.[DatabaseModifiedLog](
  9.          [ID] INT           IDENTITY(1,1) NOT NULL,
  10.          [EventType]        NVARCHAR(500) NULL,
  11.          [PostTime]         DATETIME NULL,
  12.          [ClientUser]       NVARCHAR(10) NULL,
  13.          [ServerName]       NVARCHAR(250) NULL,
  14.          [LoginName]        NVARCHAR(250) NULL,
  15.          [UserName]         NVARCHAR(250) NULL,
  16.          [DatabaseName]     NVARCHAR(250) NULL,
  17.          [SchemaName]       NVARCHAR(250) NULL,
  18.          [ObjectName]       NVARCHAR(250) NULL,
  19.          [ObjectType]       NVARCHAR(250) NULL,
  20.          [CommandText]      NVARCHAR(MAX) NULL,
  21.          [EventData]        XML NULL,
  22. PRIMARY KEY CLUSTERED
  23. ([ID] ASC)
  24. WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  25. )ON [PRIMARY]
  26. GO

2.创建数据库级别的触发器

  1. — 创建数据库级别的触发器
  2. CREATE TRIGGER [DataBase_DDL_TRIGGER]
  3. ON DATABASE
  4. FOR DDL_DATABASE_LEVEL_EVENTS
  5. AS
  6.   DECLARE @EventData        XML
  7.   DECLARE @EventType        NVARCHAR(250)
  8.   DECLARE @PostTime         DATETIME
  9.   DECLARE @SPID             NVARCHAR(6)
  10.   DECLARE @ClientUser       NVARCHAR(50)
  11.   DECLARE @ServerName       NVARCHAR(250)
  12.   DECLARE @LoginName        NVARCHAR(250)
  13.   DECLARE @UserName         NVARCHAR(250)
  14.   DECLARE @DatabaseName     NVARCHAR(250)
  15.   DECLARE @SchemaName       NVARCHAR(250)
  16.   DECLARE @ObjectName       NVARCHAR(250)
  17.   DECLARE @ObjectType       NVARCHAR(250)
  18.   DECLARE @CommandText      NVARCHAR(MAX)
  19.   SET @EventData = EVENTDATA();
  20.   SET @EventType = @EventData.value(‘(/EVENT_INSTANCE[1]/EventType[1])’, ‘NVARCHAR(250)’)
  21.   SET @PostTime = @EventData.value(‘(/EVENT_INSTANCE[1]/PostTime[1])’, ‘DATETIME’)
  22.   SET @SPID = @EventData.value(‘(/EVENT_INSTANCE[1]/SPID[1])’, ‘NVARCHAR(6)’)
  23.   SELECT @ClientUser = hostname FROM master..sysprocesses WHERE spid = @SPID
  24.   SET @ServerName = @EventData.value(‘(/EVENT_INSTANCE[1]/ServerName[1])’, ‘NVARCHAR(250)’)
  25.   SET @LoginName = @EventData.value(‘(/EVENT_INSTANCE[1]/LoginName[1])’, ‘NVARCHAR(250)’)
  26.   SET @UserName = @EventData.value(‘(/EVENT_INSTANCE[1]/UserName[1])’, ‘NVARCHAR(250)’)
  27.   SET @DatabaseName = @EventData.value(‘(/EVENT_INSTANCE[1]/DatabaseName[1])’, ‘NVARCHAR(250)’)
  28.   SET @SchemaName  = @EventData.value(‘(/EVENT_INSTANCE[1]/SchemaName[1])’, ‘NVARCHAR(250)’)
  29.   SET @ObjectName = @EventData.value(‘(/EVENT_INSTANCE[1]/ObjectName[1])’, ‘NVARCHAR(250)’)
  30.   SET @ObjectType = @EventData.value(‘(/EVENT_INSTANCE[1]/ObjectType[1])’, ‘NVARCHAR(250)’)
  31.   SET @CommandText = @EventData.value(‘(/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1])’, ‘NVARCHAR(MAX)’)
  32. — 将记录插入到数据库
  33. INSERT INTO [dbo].[DatabaseModifiedLog]
  34.            ([EventType]
  35.            ,[PostTime]
  36.            ,[ClientUser]
  37.            ,[ServerName]
  38.            ,[LoginName]
  39.            ,[UserName]
  40.            ,[DatabaseName]
  41.            ,[SchemaName]
  42.            ,[ObjectName]
  43.            ,[ObjectType]
  44.            ,[CommandText]
  45.            ,[EventData])
  46.      VALUES
  47.            (@EventType
  48.            ,@PostTime
  49.            ,@ClientUser
  50.            ,@ServerName
  51.            ,@LoginName
  52.            ,@UserName
  53.            ,@DatabaseName
  54.            ,@SchemaName
  55.            ,@ObjectName
  56.            ,@ObjectType
  57.            ,@CommandText
  58.            ,@EventData)
  59. GO
  60. SET ANSI_NULLS OFF
  61. GO
  62. SET QUOTED_IDENTIFIER OFF
  63. GO
  64. DISABLE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE
  65. GO
  66. ENABLE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE
  67. GO

3.执行DDL语句之后,查看日志表中数据,如图:

目前还没有任何评论.
您必须在 登录 后才能发布评论.