找出谁删除了某个表
当实例没有做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 ServerMSSQL11.MSSQLSERVERMSSQLLoglog_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_name
FROM sys.trace_events te
INNER JOIN sys.trace_subclass_values tsv
ON 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 est.bak'
WITH init
go
USE test
go
DROP TABLE fnlog_test
GO
USE master
go
BACKUP LOG test
TO DISK='D:SQLSample est.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 est.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 est.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 SP2&SQL Server 2012 SP1测试通过
2. trace文件是rollover的，所以要找对path，同样要从日志备份中查询的话，也要找对日志备份文件的时间
3. fn_dblog和fn_dump_dblog是Undocumented Function.