RiCo技術農場

前幾天解決的一個問題，客戶詢問如果沒有任何稽核或DDL Triggers是否有辦法查出誰刪除了資料表，

答案是肯定的，因為很久以前自己有看過透過交易紀錄檔來解決這樣的問題，

所以整理硬碟文章後終於讓我找到該文章了，

自己記錄一下順便加強印象。

ps:沒事多看文章還是有幫助的，遇到問題時，可以讓自己有更多的想法和解法，

不然這次可能會直接回答不可能，然後我的小朋友又飛了~~XD。

 

 

我先建立一個資料表，並確認該資料表object_id

create table tbA
(
c1 int identity(1,1),
c2 char(1),
CONSTRAINT PK_c1 PRIMARY KEY CLUSTERED  (c1)
)

select OBJECT_ID('tbA') 

 

使用SA連線並刪除該資料表

 

查詢交易紀錄檔

select Operation,dblog.[Transaction Name],[Transaction ID],AllocUnitName,[Server UID],dblog.SPID,dblog.[Begin Time],
p.nt_username,p.loginame,p.program_name
from fn_dblog(null, null) dblog
join sys.sysprocesses p
on p.spid=dblog.SPID 
and dblog.[Begin Time] >= '2012/10/03'
and dblog.[Transaction Name] = 'DROPOBJ'

可以看到存在一筆 SPID:57 ，loginname = sa 。

ps: fn_dblog 兩個參數為 starting LSN and ending LSN 。

 

上面已經知道某資料被 sa 刪除了，下面我們可以取得dbid和object_id，比較可惜無法取得object_name

 SELECT top(1) [Lock Information]  
 FROM fn_dblog(null, null)
 WHERE [Transaction Id] = '0000:0000c68a'
 AND [Lock Information] LIKE '%SCH_M OBJECT%';  

database id=7 ，object_id=1630628852。

 

當時我是利用該資料庫備份檔案還原到另一位置，

然後透過  sys.objects 找出 object_name

 select name 
 from sys.objects
 where object_id='1630628852'

 

測試透過AP刪除資料表

 

查詢交易紀錄檔

可以看到有相關刪除物件紀錄，不過這裡只會知道AP連線字串中的使用者ID。

 

 

參考

SQL audit alter and drop table

Finding out who dropped a table using the transaction log