Posted by Ed Murphy on 11/21/07 00:31

LaMoRt wrote:

> Is there any way to find the user who did some transaction of update
> in query analyser in certain time ?
>
> The update was done manually in query analyser and is not through
> system.
> Is there any way to check it?
>
> Any command or software to check this data as when certain table was
> updated?

You can create a trigger on one table that adds rows to a second audit
table. See http://doc.ddart.net/mssql/sql70/create_8.htm

Note that a single INSERT/UPDATE/DELETE may affect multiple rows at
once, so your trigger logic must account for this possibility.

You can get the current user's identity from USER_NAME() or
SYSTEM_USER() or SESSION_USER() - unfortunately I don't understand
how the last two are different from the first, so someone else will
have to fill that in.

[Back to original message]