数据库被删除

[chestnut]

背景：Windows server 2008服务器上的数据库总是每隔一阵子就整个库被删除。今天做了个触发器，当数据库发生create跟drop时，记录下时间跟EventData数据。但是现在想查找今天早上是谁删除了数据库？
问题：怎么查找当天是谁删了数据库？哪个用户哪个IP哪个计算机？

[阳泉酒家小当家]

那个ddl触发器，能记录时间，但是不够详细，不能记录ip地址等信息

[發糞塗牆]

sqlserver的错误日志应该有记录

[發糞塗牆]

但是可能不会那么详细

[Leon_He2014]

CREATE TABLE [dbo].[logtable](
	[session_id] [int] NULL,
	[UserName] Nvarchar(100) ,
	[connect_time] [datetime] NOT NULL,
	[client_net_address] [varchar](48) NULL,
	[client_tcp_port] [int] NULL,
	[cmd] [nvarchar](max) NULL,
	[insertTime] [datetime] default(getdate()) NULL  --日志记录插入时间
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR DROP_DATABASE
AS 

insert into [dbo].[logtable]([session_id],[UserName],	[connect_time] ,[client_net_address],
[client_tcp_port],[cmd] )
SELECT  session_id,SUSER_NAME(), connect_time,client_net_address, client_tcp_port
, EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') msg
FROM sys.dm_exec_connections
where session_id=@@SPID

GO
--DROP TRIGGER ddl_trig_database
--ON ALL SERVER;
--GO

可以在触发器中添加获取用户信息的代码，试试我上面的！ 本地测试能获取到

[KevinLiu]

引用 楼主 journeysxy 的回复:

背景：Windows server 2008服务器上的数据库总是每隔一阵子就整个库被删除。今天做了个触发器，当数据库发生create跟drop时，记录下时间跟EventData数据。但是现在想查找今天早上是谁删除了数据库？ 问题：怎么查找当天是谁删了数据库？哪个用户哪个IP哪个计算机？

用第三方的工具或者查SQL Server的日志fn_dblog,另外可以尝试一下SQL Server default trace

[唐诗三百首]

用SQL Profiler工具跟踪. 有loginname(登录名),hostname(主机名)等信息. 或用DDL触发器,参考如下文章中获取登录名,主机名,IP地址,执行的TSQL语句,程序名等信息的方法. 参考 http://blog.csdn.net/ap0405140/article/details/8896330

[chestnut]

引用 1 楼 yupeigu 的回复:

那个ddl触发器，能记录时间，但是不够详细，不能记录ip地址等信息

是这样的，如果是本机数据库客户端连服务器A上删除数据库，就会触发记下计算机名称；但是如果是计算机远程登录到服务器A上，在服务器A上删除了数据库的话，触发器只能记录下服务器A的名称跟时间，所以假设是这种情况，我想根据记录下来的时间去查找这段时间内远程登录服务器A的IP有哪些？

[阳泉酒家小当家]

引用 7 楼 journeysxy 的回复:

引用 1 楼 yupeigu 的回复:

那个ddl触发器，能记录时间，但是不够详细，不能记录ip地址等信息

是这样的，如果是本机数据库客户端连服务器A上删除数据库，就会触发记下计算机名称；但是如果是计算机远程登录到服务器A上，在服务器A上删除了数据库的话，触发器只能记录下服务器A的名称跟时间，所以假设是这种情况，我想根据记录下来的时间去查找这段时间内远程登录服务器A的IP有哪些？

这个ddl触发器，还有sql server的数据库审核，都不能记录ip地址等重要的信息，这个是一个不够完善的地方。 要不查查你的windows日志吧，这个也不一定能查询到。 先按照楼上的版主给的例子，把你的ddl触发器加强，里面记录了 用户名、ip、端口、甚至远程登录的客户端程序名称、时间等信息，防止以后再次出现这种情况。

[chestnut]

引用 6 楼 ap0405140 的回复:

用SQL Profiler工具跟踪. 有loginname(登录名),hostname(主机名)等信息. 或用DDL触发器,参考如下文章中获取登录名,主机名,IP地址,执行的TSQL语句,程序名等信息的方法. 参考 http://blog.csdn.net/ap0405140/article/details/8896330

学习了，非常感谢啊