How do I access a user's application username when that application connects to the DB? I'd like to audit changes to a table in SQLServer using a trigger and an audit table to do so. End-users connect to the database through an application. If I use SYSTEM_USER to obtain the end user's username, then it returns the name of the application that is connecting to the database, and not the user's unique login name for that application.

What would be a standard approach to capturing the user's unique application login name in this scenario?
Although I noted a potential duplicate question in my comments above, Microsoft has introduced new features to make the passing of (essentially "session") information from a client application to the database easier, with the introduction of new features in SQL Server 2016.

As noted before, CONTEXT_INFO was the solution prior to 2016 - but it is fraught with difficulties.

From 2016 onward, SESSION_CONTEXT has been introduced.

Rather than try and summarise this myself, there is an excellent article describing both methods, their shortcomings and providing SQL examples on their usage. I'm sure there's ample information there to work through a solution to this problem.