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

Posted on by

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

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

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

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

解决办法:

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

  1. USE [QSBN]
  2. GO
  4. SET ANSI_NULLS ON
  5. GO
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  10. — 创建数据库修改日志表
  11. CREATE TABLE dbo.[DatabaseModifiedLog](
  12.          [ID] INT           IDENTITY(1,1) NOT NULL,
  13.          [EventType]        NVARCHAR(500) NULL,
  14.          [PostTime]         DATETIME NULL,
  15.          [ClientUser]       NVARCHAR(10) NULL,
  16.          [ServerName]       NVARCHAR(250) NULL,
  17.          [LoginName]        NVARCHAR(250) NULL,
  18.          [UserName]         NVARCHAR(250) NULL,
  19.          [DatabaseName]     NVARCHAR(250) NULL,
  20.          [SchemaName]       NVARCHAR(250) NULL,
  21.          [ObjectName]       NVARCHAR(250) NULL,
  22.          [ObjectType]       NVARCHAR(250) NULL,
  23.          [CommandText]      NVARCHAR(MAX) NULL,
  24.          [EventData]        XML NULL,
  25. PRIMARY KEY CLUSTERED
  26. ([ID] ASC)
  27. WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  28. )ON [PRIMARY]
  29. 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)
  20.   SET @EventData = EVENTDATA();
  21.   SET @EventType = @EventData.value(‘(/EVENT_INSTANCE[1]/EventType[1])’, ‘NVARCHAR(250)’)
  22.   SET @PostTime = @EventData.value(‘(/EVENT_INSTANCE[1]/PostTime[1])’, ‘DATETIME’)
  23.   SET @SPID = @EventData.value(‘(/EVENT_INSTANCE[1]/SPID[1])’, ‘NVARCHAR(6)’)
  24.   SELECT @ClientUser = hostname FROM master..sysprocesses WHERE spid = @SPID
  25.   SET @ServerName = @EventData.value(‘(/EVENT_INSTANCE[1]/ServerName[1])’, ‘NVARCHAR(250)’)
  26.   SET @LoginName = @EventData.value(‘(/EVENT_INSTANCE[1]/LoginName[1])’, ‘NVARCHAR(250)’)
  27.   SET @UserName = @EventData.value(‘(/EVENT_INSTANCE[1]/UserName[1])’, ‘NVARCHAR(250)’)
  28.   SET @DatabaseName = @EventData.value(‘(/EVENT_INSTANCE[1]/DatabaseName[1])’, ‘NVARCHAR(250)’)
  29.   SET @SchemaName  = @EventData.value(‘(/EVENT_INSTANCE[1]/SchemaName[1])’, ‘NVARCHAR(250)’)
  30.   SET @ObjectName = @EventData.value(‘(/EVENT_INSTANCE[1]/ObjectName[1])’, ‘NVARCHAR(250)’)
  31.   SET @ObjectType = @EventData.value(‘(/EVENT_INSTANCE[1]/ObjectType[1])’, ‘NVARCHAR(250)’)
  32.   SET @CommandText = @EventData.value(‘(/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1])’, ‘NVARCHAR(MAX)’)
  34. — 将记录插入到数据库
  35. INSERT INTO [dbo].[DatabaseModifiedLog]
  36.            ([EventType]
  37.            ,[PostTime]
  38.            ,[ClientUser]
  39.            ,[ServerName]
  40.            ,[LoginName]
  41.            ,[UserName]
  42.            ,[DatabaseName]
  43.            ,[SchemaName]
  44.            ,[ObjectName]
  45.            ,[ObjectType]
  46.            ,[CommandText]
  47.            ,[EventData])
  48.      VALUES
  49.            (@EventType
  50.            ,@PostTime
  51.            ,@ClientUser
  52.            ,@ServerName
  53.            ,@LoginName
  54.            ,@UserName
  55.            ,@DatabaseName
  56.            ,@SchemaName
  57.            ,@ObjectName
  58.            ,@ObjectType
  59.            ,@CommandText
  60.            ,@EventData)
  61. GO
  63. SET ANSI_NULLS OFF
  64. GO
  66. SET QUOTED_IDENTIFIER OFF
  67. GO
  69. DISABLE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE
  70. GO
  72. ENABLE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE
  73. GO

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

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注