Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deepgfishing
Starting Member
12 Posts |
 Posted - 2009-04-23 : 15:35:01
|
| Hello.Anyone familiar with Change Data Capture?My question is how do I track the user who deleted a record in the database. I understand the __$operation will have a 1 for delete records, but I need to determine the user that actually deleted the record. We use a single SQL Server login for our web application.We have LastModifiedUser column and LastModifiedDate columns in our database tables so we can track inserts and updates just fine because for inserts the LastModifiedUser will be the suer that created the row, and in updates we can look at the before and after values to see who updated the record. The problem is when a user deletes a record there is no way for me to track who the user that deleted the record is.Would i have to create my own triggers for this instance?I thought about running an update statement before I run the delete statement to get the information in the change tables but that is a hack.I also thought of adding an IsDeleted Column and setting IsDeleted to 1 when performing a delete and then writing a SQL job to come and clean up all my IsDeleted marked rows. This way I could query CDC updated rows where the IsDeleted Flag was set to 1 and get the LastModifiedUser.Any other ideas? |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-04-28 : 10:17:21
|
| I have only seen a few demonstrations of CDC, and I think you will have to create your own trigger to deal with this instance. If the was coming in on their own credentials, then you could use CDC, but where the web application is a proxy for the users, you will have to save the data in another way. |
 |
|
|
|
|
|
Change Data Capture Question