Using Trigger to Track Who Changed Table data

SQL Server Administration
41

Also, if it's per user, there might be some dynamic sql that is updating the table and setting the flag to true, but has no filter on it, so it's updating the entire table

42

True, @mike01 . This is SQL Server 2008 Standard so it has limited extended events. Do you happen to know of an example showing how to configure an extended event for updates to a specific table?

43

@JeffModen I created it with WITH EXECUTE AS OWNER
FOR UPDATE, delete
Then when I try to update a row.......
Get Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

44

@DBAForever... who or what owns the database and what privs does that database owner have.

And, just to be sure, you use WITH EXECUTE AS OWNER exactly and not something like WITH EXECUTE AS DBO, correct?

45

@jeffw8713 I used WITH EXECUTE AS OWNER on the create trigger and the db owner is sa
Thanks

46

FYI - wrong Jeff :slight_smile:

I would recommend an upgrade - 2008 R2 (assuming this - if not R2 it is even worse) is out of support and has been out of support for quite a while.

Since extended events were not fully configurable/available in that version - you can use a server-side trace instead. However, I am not sure that is going to help - or that a trigger is going to help, unless you know for sure that every user is actually setup as a login/user in SQL Server. My gut instinct is that isn't the case - and all users are using a generic login.

As many have pointed out - you have included a join to the table tblUsers which isn't necessary. The trigger is on that table so you already have access to all of the columns in the inserted table.

As I stated before - I would put a check in this type of trigger and only set the trigger for update (no need to set it on delete) and only perform the insert if the column is included in the update statement.

But then again - I wouldn't bother with a trigger. I would use a server-side trace and filter by the statement including that column - or filter the statement for the table. I would start with Batch:Completed - if that didn't find it, then RPC:Completed - and if not found there to statement completed.

2 Likes
47

@jeffw8713 We will migrate away from this system within 4 months so we will not upgrade form SLQ 2008. I have implemented jobs to immediately alert us when the users get locked. (I just count the rows which are locked it true every 3 minutes and email if over a threshold) That's working well although we still don't know who/what is doing it.