question

Sheik avatar image
Sheik 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?
updatetable
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

is the database in full or simple recovery?
0 Likes 0 ·
It is in Full Recovery Model
0 Likes 0 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered

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/

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you... This shows the time of the updation, how do I get the user who update the record?
0 Likes 0 ·
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
0 Likes 0 ·
No one of the shortcomings of CDC is that it does not contain user info.
0 Likes 0 ·
Also implementing CDC or any kind of change capture now is not going to help the OP find what has already happened.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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 -
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Good one Kev :-) like to add a constraint - pocket NULL
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.