Is there a way of storing user's id when he triggers a SQL trigger?

Question

Is there any way of tracing user's identity when he/she triggers a a delete trigger on T-SQL 2005, e.g. with UNIQUEIDENTIFIER? If I do record the UNIQUEIDENTIFIER, how do I trace it back to the user?

Answer 1

You could store @@SPID at the time of the trigger and trace back to the user using sp_who

Answer 2

take a look at suser_sname() or user_name() for windows/sql login, of course if everyone uses the same login this won't help you

the deleted pseudo table will have all the rows that are to be deleted so in the worst case you can offload those somewhere else in case you need to get them back