Capture USER_ID For Delete Operation

Question

Hi All,

We currently use Auditing triggers to track all of our DB changes. In order to capture the user_id for the person that submitted the delete we have to use an update and then have the trigger perform a delete so we can capture that USER_ID.

Does anyone else use a different approach or have a better approach for this process?

Thanks,

--S

Answer 1

If you are using Windows Authentication (either 2-tier, or impersonation) then you can use SUSER_NAME() and SUSER_ID()

select SUSER_NAME(), USER_ID()

Answer 2

For a direct connection from the client, use SUSER_SNAME() or similar in the DELETE trigger.

For an indirect (eg via a web site), use SET CONTEXT_INFO (answers by me before) to pass in the executing user details. This can be set via code in the web site or via stored proc parameters. This can also be used for a direct connection too.