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

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

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

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

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

解决办法:

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

USE [QSBN] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- 创建数据库修改日志表 CREATE TABLE dbo.[DatabaseModifiedLog]( [ID] INT IDENTITY(1,1) NOT NULL, [EventType] NVARCHAR(500) NULL, [PostTime] DATETIME NULL, [ClientUser] NVARCHAR(10) NULL, [ServerName] NVARCHAR(250) NULL, [LoginName] NVARCHAR(250) NULL, [UserName] NVARCHAR(250) NULL, [DatabaseName] NVARCHAR(250) NULL, [SchemaName] NVARCHAR(250) NULL, [ObjectName] NVARCHAR(250) NULL, [ObjectType] NVARCHAR(250) NULL, [CommandText] NVARCHAR(MAX) NULL, [EventData] XML NULL, PRIMARY KEY CLUSTERED ([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

 

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

-- 创建数据库级别的触发器 CREATE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @EventData XML DECLARE @EventType NVARCHAR(250) DECLARE @PostTime DATETIME DECLARE @SPID NVARCHAR(6) DECLARE @ClientUser NVARCHAR(50) DECLARE @ServerName NVARCHAR(250) DECLARE @LoginName NVARCHAR(250) DECLARE @UserName NVARCHAR(250) DECLARE @DatabaseName NVARCHAR(250) DECLARE @SchemaName NVARCHAR(250) DECLARE @ObjectName NVARCHAR(250) DECLARE @ObjectType NVARCHAR(250) DECLARE @CommandText NVARCHAR(MAX) SET @EventData = EVENTDATA(); SET @EventType = @EventData.value('(/EVENT_INSTANCE[1]/EventType[1])', 'NVARCHAR(250)') SET @PostTime = @EventData.value('(/EVENT_INSTANCE[1]/PostTime[1])', 'DATETIME') SET @SPID = @EventData.value('(/EVENT_INSTANCE[1]/SPID[1])', 'NVARCHAR(6)') SELECT @ClientUser = hostname FROM master..sysprocesses WHERE spid = @SPID SET @ServerName = @EventData.value('(/EVENT_INSTANCE[1]/ServerName[1])', 'NVARCHAR(250)') SET @LoginName = @EventData.value('(/EVENT_INSTANCE[1]/LoginName[1])', 'NVARCHAR(250)') SET @UserName = @EventData.value('(/EVENT_INSTANCE[1]/UserName[1])', 'NVARCHAR(250)') SET @DatabaseName = @EventData.value('(/EVENT_INSTANCE[1]/DatabaseName[1])', 'NVARCHAR(250)') SET @SchemaName = @EventData.value('(/EVENT_INSTANCE[1]/SchemaName[1])', 'NVARCHAR(250)') SET @ObjectName = @EventData.value('(/EVENT_INSTANCE[1]/ObjectName[1])', 'NVARCHAR(250)') SET @ObjectType = @EventData.value('(/EVENT_INSTANCE[1]/ObjectType[1])', 'NVARCHAR(250)') SET @CommandText = @EventData.value('(/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1])', 'NVARCHAR(MAX)') -- 将记录插入到数据库 INSERT INTO [dbo].[DatabaseModifiedLog] ([EventType] ,[PostTime] ,[ClientUser] ,[ServerName] ,[LoginName] ,[UserName] ,[DatabaseName] ,[SchemaName] ,[ObjectName] ,[ObjectType] ,[CommandText] ,[EventData]) VALUES (@EventType ,@PostTime ,@ClientUser ,@ServerName ,@LoginName ,@UserName ,@DatabaseName ,@SchemaName ,@ObjectName ,@ObjectType ,@CommandText ,@EventData) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO DISABLE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE GO ENABLE TRIGGER [DataBase_DDL_TRIGGER] ON DATABASE GO

 

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