SQL Server: Modifying the "Application Name" property for auditing purposes

Question

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?

Answer 1

I use SET CONTEXT_INFO for this. It's just what you need.

Answer 2

It certainly seems like a feasible solution, although you'll need to inject the username into the connection string each time your application loads. Note that this solution probably wouldn't work with a web application, as your connection string will be different each time, which could lead to huge connection pooling issues.

Another option is to retrieve the hostname/IP address (SELECT host_name() ) and store that instead.

You wouldn't necessarily need a new parameter on each stored procedure, as you can modify each stored procedure (or the trigger) to automatically insert the App_Name/Hostname.

A potential drawback is that any modifications performed via Management Studio won't have the custom App_Name, and you'll be left with "Microsoft Management Studio" as the user.