Q:

Finding the AspNetUser from within a trigger

I am working with an ASP.NET MVC application. In this application I am wanting to update some data fields on the record just written - including the userID of the person responsible for the change. The problem is that I don't know how to find out who has changed the record.
I have used CURRENT_USER in my script but that always returns the current SQL_SERVER security user - in this case "dbo" as that is the user that ASP.NET MVC connects to the database with. This would mean that all changes were made by the same user. If I can get the AspNetUser record ID then I'll be okay.
Any Ideas? Do I need to find a way to add unique users for all web logins? That is not mentioned anywhere, so I suspect that it's not expected. 
Thanks

A:

From a SQL Server perspective the "user" of the database is your MVC application, not a user logged in to your web application. The only way to change this is to create database logins for all web application users, but I wouldn't recommend this approach.
IMHO it is best to move the mentioned functionallity from SQL Server triggers to your data access layer (in .NET, your "repositories" or whatever data access pattern you are using).

