Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

I'm working on the server using SQL Server Management Studio 2005. Is it possible to identify the person who made changes in the SQL server Management Studio 2005 tables data?

share|improve this question

migrated from stackoverflow.com Jul 5 '13 at 9:14

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Jon Seigel, RolandoMySQLDBA, dezso, Paul White, StanleyJohns Jul 6 '13 at 8:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
By default, SQL Server doesn't track such information. –  Damien_The_Unbeliever Jul 5 '13 at 7:24
    
This question appears to be off-topic because "we can't answer it"! –  gbn Jul 5 '13 at 7:29
    
really? i try this codes –  user2549456 Jul 5 '13 at 7:42
1  
If you want to add more to your question, use the edit link below it. That would also let you format your code (using the {} button) - as you may have noticed, code in comments isn't very readable. –  Damien_The_Unbeliever Jul 5 '13 at 7:55
4  
fn_my_permissions tells you what you're allowed to do right now. It a) doesn't tell you whether you've exercised any of those permissions recently, nor does it b) tell you what permissions you had 5 minutes ago. (And similar comments for other functions that let you view others permissions). So I don't see how it helps with "identify the person who made changes" which is a question about things in the past. –  Damien_The_Unbeliever Jul 5 '13 at 8:03

1 Answer 1

You have to log the data in some way manually. My favorite is to include CREATE and UPDATE columns on each table as such:

ALTER TABLE TableName ADD [CREATE_TIMESTAMP] datetime NULL DEFAULT (getdate())
ALTER TABLE TableName ADD [CREATE_USERID] varchar(30) NULL DEFAULT (suser_sname())
ALTER TABLE TableName ADD [UPDATE_TIMESTAMP] datetime NULL DEFAULT (getdate())
ALTER TABLE TableName ADD [UPDATE_USERID] varchar(30) NULL DEFAULT (suser_sname())

Then add a trigger to force the update of the UPDATE columns. After that you can tell who inserted or updated a row. Still can't tell a delete though. You would need a log table for that. Basically create a table TableName_Log that has the same structure as TableName then a DELETE trigger that moves the deleted rows (updated too if you want) into the log table.

share|improve this answer