asked
How to identify the user who updated the record in a particular table in SQL Server 2005
One of our production table got updated... particularly 2 fields have been updated for most of the record. we have identified the rows with the help of backup. There is no timestamp or audit log maintained for this table. Is there any way to find out who and when the table got updated?
You can use this query:-
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'databasename')
AND OBJECT_ID=OBJECT_ID('tablename')
For more details refer to this link :-
http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/
Hi Sheik I dont think we can get that. I think in later versions we have something called change data capture that might give us the user related info. Thanks Lokesh
Also implementing CDC or any kind of change capture now is not going to help the OP find what has already happened.
If you are comfortable enough, you could look into the transaction log using the ***undocumented*** fn_dump_dblog to see the user that made the change. I'd refer you to here:
http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/ to get a good understanding of how to do this. Alternatively, put your hand in your pocket and buy a tool that does this for you - such as ApexSQL Log -
16 People are following this question.
