绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
SQLServer中如何找出哪个用户删除了某表
2023-02-20 17:30:57

一、总结

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

              分享好友

              分享这个小栈给你的朋友们,一起进步吧。

              Microsoft SQL Server
              创建时间:2022-03-30 11:29:11
              Microsoft SQL Server
              展开
              订阅须知

              • 所有用户可根据关注领域订阅专区或所有专区

              • 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

              • 专区发布评论属默认订阅所评论专区(除付费小栈外)

              技术专家

              查看更多
              • itt0918
                专家
              戳我,来吐槽~