As we do not implement the users of our applications as users in SQL server, when the application server connects to a database each application always uses the same credentials to attach to each database.
This presents an auditing problem. Using triggers, we want to store every update, insert and delete and attribute each to a particular user. One possible solution is to add an "updated by user" column to every table and update this every time. This means a new column on every table and a new parameter on every stored procedure. It also means you can only do soft deletes.
Instead of this I propose using the Application Name property of the connection string and reading this with the App_Name() property inside the trigger. I tested this with a simple app and it seems to work (the format could be as so: App=MyApp|User=100).
The question for you guys is, is this a bad idea and do you have a better one?