36-SQLServer中如何找出哪个用户删除了某表

一、总结

1、当没有部署触发器和开启审计的情况，想知道某张表是被哪个用户删除的，可以使用系统函数fn_dblog结合系统的默认跟踪找到；

2、需要注意的是系统默认提供5个跟踪文件，默认大小都是20MB，当实例重启的时候或者达到最大值的时候，会重新生成新的文件，

     将最早的跟踪文件删除，依次滚动更新，所以删除表后间隔的时间太长，也是找不到结果的；

3、fn_dblog函数简介

　　* 使用fn_dblog函数我们可以查看当前数据库的事物日志文件的记录（活动的日志，备份走的日志用该函数查不到）；

       * 官方并未公开此函数的用法，在生产环境，慎用此命令！！！；

　　* 2个参数：

　　　　参数1：日志开始序列号位置（LSN），输入NULL，表示从日志开始位置返回结果；

　　　　参数2：日志结束序列号位置（LSN），输入NULL，表示查找到日志最后的结束位置；

4、通过sys.fn_dblog最后定位到的结果，只能显示object_id，找不到具体的表名，如果有多个用户做了大量的drop操作时，

     找出的结果也非常多，可能得不到想要的结果；

5、如果在删除表时，用户指定了事物名称，在使用sys.fndblog查找结果的时候，[Transaction Name]的值必须使用用户指定的事物名称才能出结果；

 

 

 6、如果在删除表时，用户手动开启了事物，但是并没有指定事物名称，在使用sys.fndblog查找结果的时候，[Transaction Name]的值要使用user_transaction才能有结果；

 

 

 

 

二、操作步骤

1、准备测试数据

use test
go
create table student (id int,name nvarchar(20))
go

insert into student values(1,'jack')
insert into student values(2,'rose')

select * from student

 2、查看student表的object_id(看和一会找出来的是否一致)

 命令：select * from test.sys.objects where name='student'

 3、创建一个具有test库dba权限的用户dbinfo

4、使用dbinfo用户登录数据库删除student表

 

 5、使用系统函数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

 6、根据事物ID，找出被删除的对象

select  [Lock Information] from sys.fn_dblog(NULL,NULL)

where [Lock Information] like '%SCH_M OBJECT%' and [Transaction ID]='0000:00000d8e'

go

  结果：HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 7:597577167:0 

 结果中OBJECT后面的信息就是删除的对象的信息：

7---->代表DB_ID

597577167------>代表object_id，也就是被删除对象的object_id（发现和上面我们删除之前查询的object_id一致， 显然现在通过这个object_id已经查不到结果了，因为已经删除了）

7、找出SQLServer中默认的trace的path路径

命令：select * from sys.traces where is_default =1 

 8、根据trace path，事务ID、object_id、开始时间、SPID等信息找到对应的trace信息

select  DatabaseID,NTUserName,HostName,ApplicationName,LoginName,

          SPID,ObjectID,StartTime, EventClass,EventSubClass

from  sys.fn_trace_gettable('C:\Program Files\MicrosoftSQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\log_148.trc',1)

where SPID = 61

         and  StartTime >= '2020/07/23 11:00:15:780' 

         and ObjectID = '597577167'

go

 

参考网址：

 https://www.cnblogs.com/Joe-T/p/3191416.html

 

 

 

***************************************************

如下是个人开发系统，欢迎大家体验，纯属个人爱好，想一块玩的，私信。

易本浪账：www.jialany.com  

***************************************************