找出谁删除了某个表

当实例没有做DDL Trigger和其它一些监控时，如何知道谁删除了某个表？通过系统函数fn_dblog,fn_dump_dblog和默认跟踪可以找到。

1. 创建测试环境：新建个表，插入一条数据，然后drop掉

CREATE DATABASE test
go
USE test
go
CREATE TABLE dbo.fnlog_test
(id INT IDENTITY ,val VARCHAR(10) DEFAULT 'x')
GO
CREATE CLUSTERED INDEX IX_ft_id
ON dbo.fnlog_test (ID)

GO
INSERT INTO dbo.fnlog_test
VALUES (DEFAULT )
GO
DROP TABLE fnlog_test
GO

2. 通过sys.fn_dblog,找出相关信息：

USE test
go
SELECT [Transaction ID],[Transaction Name],[Begin Time],[Server UID],SPID
FROM sys.fn_dblog(NULL,null)
WHERE [Transaction Name]='DROPOBJ'
go

 

3. 上一步中这里得到了事务ID，开始时间，Suid,SPID等，但是执行删除的SPID可以已经logout或者被重用了。所以要找出“当时”的这个SPID。

先根据事务ID，找出被删除的对象吧。查询结果的“OBJECT: 9:245575913:0”，9是DB_ID,245575913是object_id,就是被删除的表的object_id.

SELECT TOP(1) [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Lock Information] LIKE '%SCH_M OBJECT%' AND [Transaction ID]='0000:000002e7'
go

4. 通常SQL Server实例安装后会开启一个默认跟踪(Default Trace)，这个跟踪会记录一引起级别较高的重要信息。先找到默认跟踪

SELECT id,status,path FROM sys.traces
WHERE is_default=1

5. 根据前几步中得到的trace path,事务ID，开始时间，SPID，object_id，通过默认跟踪得到进一步的信息：

SELECT DatabaseID,NTUserName,HostName,ApplicationName,LoginName,
SPID,ObjectID,StartTime, EventClass,EventSubClass
FROM sys.fn_trace_gettable('D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_10.trc',1)
WHERE SPID=52 AND  StartTime>'2013/07/15 11:32:44:133' AND ObjectID =245575913
GO

这一步中就得到了谁删除了这个表的更具体信息了。需要说明一下的是EventClass=47,EventSubclass=(0,1)，这记录了跟踪事件的操作。

SELECT te.trace_event_id,te.name,tsv.subclass_value,tsv.subclass_nameFROM sys.trace_events teINNER JOIN sys.trace_subclass_values tsvON te.trace_event_id=tsv.trace_event_id WHERE te.trace_event_id=47 AND tsv.subclass_value IN(0,1)

6. 如果是生产环境的，事务日志可能被截断而被重用覆盖了。这里就需要从日志备份中读取日志信息来定位。需要用到fn_dump_dblog.

  重新构建测试环境：

CREATE DATABASE test
go
USE test
go
CREATE TABLE dbo.fnlog_test
(id INT IDENTITY ,val VARCHAR(10) DEFAULT 'x')
GO
CREATE CLUSTERED INDEX IX_ft_id
ON dbo.fnlog_test (ID)

GO
INSERT INTO dbo.fnlog_test
VALUES (DEFAULT )
GO
USE master
go
BACKUP DATABASE test
TO DISK='D:\SQLSample\test.bak'
WITH init
go
USE test
go
DROP TABLE fnlog_test
GO
USE master
go
BACKUP LOG test
TO DISK='D:\SQLSample\test.bck'
WITH init
go
2. 和3. 的查询要换成fn_dump_dblog，其它的步骤是一样的。这里我另外做的测试，所以事务ID与前面不同了。

SELECT [Transaction ID],[Transaction Name],[Begin Time],[Server UID],SPID FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\SQLSample\test.bck',  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Transaction Name] LIKE '%DROPOBJ%'SELECT TOP(1) [Lock Information]FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\SQLSample\test.bck',  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Lock Information] LIKE '%SCH_M OBJECT%' AND [Transaction ID]='0000:000002b8'

总结：

  1. 在SQL Server 2008 R2&SQL Server 2012 SP1测试通过

  2. trace文件是rollover的，所以要找对path，同样要从日志备份中查询的话，也要找对日志备份文件的时间

  3. fn_dblog和fn_dump_dblog是Undocumented Function.