SQL高手进来帮忙看看!!!!!!!!!!!!!!!!!!!!! 开发代码的时候突然发现,团队里面不知道谁把某一个表名改了,导致前台出错,结果谁都不承认如何查出是谁干的? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 如果没有源代码管理工具而且以前也没做过监控,现在要查很难了。如果实在没有,那么这次就算了,把监控和源代码管理工具用上。登录开发服务器的时候用windows登录,这样就没啥好狡辩了。 sql 不是有日志日志的工具,LOG Explorerhttp://www.cnblogs.com/wangbinjr/archive/2010/05/04/2354215.html sql 不是有日志查看的工具,LOG Explorer 如果只用了sql身份登录来改,那这个工具没什么用处。这个用来恢复还差不多,但是也有诸多前提条件的。源代码管理工具:visual sourcesafe或者tfs,要求所有对数据库的修改都要经过这个,这样不仅可以恢复,也可以轻易查出谁做了什么操作,对于正常一点的开发公司,都 不可缺少,当然工具有很多,不仅仅这两个。监控:做DDL触发器,可以查看谁改了东西。参考:ddl触发器:IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')DISABLE TRIGGER [DDLTriggertTrace] ON DATABASEGO/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 12/21/2012 14:16:36 ******/IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')DROP TRIGGER [DDLTriggertTrace] ON DATABASEGO/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 12/21/2012 14:16:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [DDLTriggertTrace] ON DATABASE--捕获存储过程、视图、表的创建、修改、删除动作 FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLEAS BEGIN SET NOCOUNT ON ; DECLARE @EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息,以XML格式保存。 DECLARE @ip VARCHAR(32) = ( SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID ) ; INSERT AuditDB.dbo.DDLEvents ( EventType , EventDDL , EventXML , DatabaseName , SchemaName , ObjectName , HostName , IPAddress , ProgramName , LoginName ) SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') , @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') , @EventData , DB_NAME() , @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)') , @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)') , HOST_NAME() , @ip , PROGRAM_NAME() , SUSER_SNAME() ; ENDGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO存放的表:/****** Object: Table [dbo].[DDLEvents] Script Date: 12/21/2012 14:17:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[DDLEvents]( [EventDate] [datetime] NOT NULL, [EventType] [nvarchar](64) NULL, [EventDDL] [nvarchar](max) NULL, [EventXML] [xml] NULL, [DatabaseName] [nvarchar](255) NULL, [SchemaName] [nvarchar](255) NULL, [ObjectName] [nvarchar](255) NULL, [HostName] [varchar](64) NULL, [IPAddress] [varchar](32) NULL, [ProgramName] [nvarchar](255) NULL, [LoginName] [nvarchar](255) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[DDLEvents] ADD DEFAULT (getdate()) FOR [EventDate]GO 如何在列冲修改数据 SQL 2005 用户架构删除问题,在线等等中!急 函数返回多个值? 请教一句sql MSSQL2000某重复行设置 请问表连接查询的一个问题 不同数据库之间如何移迁,例如sqlserver2000移到Oracle 提高查询速度方法总结 免费为您开发MIS系统 请教3个简单的问题100分!!!不够再加!!! 一条SQL语句的问题 如何连接外网 SQL 2005 实例
http://www.cnblogs.com/wangbinjr/archive/2010/05/04/2354215.html
监控:做DDL触发器,可以查看谁改了东西。参考:
ddl触发器:
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')
DISABLE TRIGGER [DDLTriggertTrace] ON DATABASEGO/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 12/21/2012 14:16:36 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')DROP TRIGGER [DDLTriggertTrace] ON DATABASE
GO/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 12/21/2012 14:16:36 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW,
ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息,以XML格式保存。
DECLARE @ip VARCHAR(32) = ( SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
) ; INSERT AuditDB.dbo.DDLEvents
( EventType ,
EventDDL ,
EventXML ,
DatabaseName ,
SchemaName ,
ObjectName ,
HostName ,
IPAddress ,
ProgramName ,
LoginName
)
SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'NVARCHAR(100)') ,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'NVARCHAR(MAX)') ,
@EventData ,
DB_NAME() ,
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',
'NVARCHAR(255)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
'NVARCHAR(255)') ,
HOST_NAME() ,
@ip ,
PROGRAM_NAME() ,
SUSER_SNAME() ;
ENDGOSET ANSI_NULLS OFF
GOSET QUOTED_IDENTIFIER OFF
GO
存放的表:/****** Object: Table [dbo].[DDLEvents] Script Date: 12/21/2012 14:17:31 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[DDLEvents](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY]GOSET ANSI_PADDING OFF
GOALTER TABLE [dbo].[DDLEvents] ADD DEFAULT (getdate()) FOR [EventDate]
GO