Database Audit Log using Triggers - User information

Question

I'm contemplating using AFTER INSERT, DELETE, UPDATE Triggers to capture changes to one of my databases. One thing I want is to capture name of the user who made the change.

Since I'm using a dedicated SQL account to connect to the database (from ASP.NET MVC), I don't believe SUSER_SNAME() will provide accurate information to me.

Is there anyway for me to feed in the username from ASP.NET MVC side to the trigger?

ASP.NET side is configured for Windows Authentication against Active Directory.

Answer 1

Take a look at Set Context_Info. I've used this successfully in the past when auditing and using sql authentication. The idea is that you set the in the context the logged on user, then in the DB Trigger you retrieve this info from the context and store it in the DB. If you forget to set the user in the context, you can always fall back to suser_name (although, not very useful for auditing, but can be useful when making changes directly in SSMS). Google "Audit Trail Set Context_Info" and you will find some examples.

Like this one and this other one

Answer 2

You could use Thread.CurrentPrincipal to get the Windows Account and pass this as a parameter to your queries, and therefore, available to Triggers.

 var userName = Thread.CurrentPrincipal.Identity.Name;