Finding delete or update records

I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero across a whole table. We have a team of four people who have the permission and who are responsible for updating the database who could have done this. Disappointingly no one has admitted to carrying out the changes.

Going forward I would like to be able to have a record of these transactions. I was wondering what do other people use to track these changes? Do they use software that tracks changes or do you create stored procedures or trace files? If anyone has this set up at their facility I would like to know what they use. The trace files do have the information I am after such as login name machine number and the sql statement so it will give me the information if I set them up in advance.

I have copies of the database and the transaction logs when these changes took place. Is there anything I can do with these old files to help track down the culprit? Thanks in advance to anyone who replies. We are using SQL server 2005.

fn_dblog is the way to look backwards as the other commentators have said.

Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This may mean that you'd need to make application changes depending on how they're currently accessing the data. Which may or may not be possible for you.

If you can't do that, then a quick and simple way with SQL 2005 is going to be using a trigger to do some DML auditing at the table level. (SQL Server 2008 onwards have auditing tools built in).

A simple solution for your issue might be:

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

This will fire any time a query attempts to update the table, or delete from the table. It uses DBCC inputbuffer ( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) to get the issued command. This gives you a table populated with all the update and delete statements issued against a particular table. Plus it records who issued statement, and where and when it was.

Now this could be a lot of logging data on a busy table, so this could be restricted to just catch 'bad' queries (say those that update/delete 1000+ rows) by altering the trigger to:

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

This logs less data, but the trigger still needs to 'evaluate' for every operation, so may have a performance impact which will need to be measured and tested. This will also miss the actions if the user submits lots of individual statements, ie;

won't catch:

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

will catch

delete from tbl_example where id>0 and id<1001

Hope this is of some help for the future.

StackExchange Database Administrators Q#28339, answer score: 5