查找刪除或更新記錄

最近幾個月我發生了三起事件，其中表中的記錄已被刪除或整個表中的值更新為零。我們有一個由四人組成的團隊，他們擁有權限並負責更新本可以執行此操作的數據庫。令人失望的是，沒有人承認進行了這些更改。

展望未來，我希望能夠記錄這些交易。我想知道其他人用什麼來跟踪這些變化？他們是否使用跟踪更改的軟體，或者您是否創建儲存過程或跟踪文件？如果有人在他們的設施中設置了這個，我想知道他們使用什麼。跟踪文件確實有我之後的資訊，例如登錄名機器號和 sql 語句，所以如果我提前設置它們，它會給我資訊。

當這些更改發生時，我有數據庫和事務日誌的副本。我可以用這些舊文件做些什麼來幫助追查罪魁禍首嗎？提前感謝任何回复的人。我們使用的是 SQL Server 2005。

fn_dblog正如其他評論員所說，這是向後看的方式。

只允許使用者通過儲存過程修改數據是首先防止這種情況發生的好方法，因為您可以添加邏輯來防止使用者大量修改他們不應該的記錄，或者確保他們必須提供正確的值更新。這可能意味著您需要根據應用程序目前訪問數據的方式進行更改。這對你來說可能也可能不可能。

如果您不能這樣做，那麼使用 SQL 2005 的一種快速而簡單的方法是使用觸發器在表級別執行一些 DML 審計。（SQL Server 2008 及更高版本已內置審核工具）。

您的問題的一個簡單解決方案可能是：

drop table audit_test
go
create table audit
(
uname varchar(50),
[date] datetime,
what nvarchar(4000),
host varchar(50),
)
go 

create trigger ddlcheck on tbl_example 
for update, delete
as
declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp

這將在查詢嘗試更新表或從表中刪除時觸發。它使用DBCC inputbuffer( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) 來獲取發出的命令。這為您提供了一個表，其中填充了針對特定表發出的所有更新和刪除語句。此外，它還記錄了誰發表了聲明，以及發表聲明的時間和地點。

現在這可能是繁忙表上的大量日誌記錄數據，因此可以通過將觸發器更改為：

create trigger ddlcheck on tbl_example 
for update, delete
as
declare @cnt integer
select @cnt=count(1) from deleted
if @cnt>1000
begin
 declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
 insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
 insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp
end

這會記錄較少的數據，但觸發器仍需要對每個操作進行“評估”，因此可能會對性能產生影響，需要對其進行測量和測試。如果使用者送出大量單獨的語句，這也將錯過操作，即；

不會抓住：

delete from tbl_example where id=1
delete from tbl_example where id=2
.....
delete from tbl_exampe where id=1000

會抓住

delete from tbl_example where id>0 and id<1001

希望這對未來有所幫助。

引用自：https://dba.stackexchange.com/questions/28339