views:

1285

answers:

3

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?

A: 

We use the Application Name property to control auditing triggers and have not seen any problems using it, and haven't noticed any speed issues (though in our case, we're specifically not auditing for certain applications, so its hard to measure how much time not doing something takes :))

Ch00k
That's good to hear. By the sounds of it you are using the same application name each time. I'm proposing modifying the application name for each connection to identify the current user. Can you see any problem with this (we do not pool connections)?
Chris Simpson
We use two different Application Names, one for when we want auditing, and one for when we don't (because whatever is using the connection is handling the auditing manually).
Ch00k
+1  A: 

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.

Jim McLeod
Though we used to do this, we no longer use connection pooling so each connection made is individual. The hostname/ip address would not work because this would come from the application server not the client.
Chris Simpson
If only I had stumbled across this answer a few years ago... We use the App Name for exactly this purpose, for exactly this reason, and have connection pooling issues exactly as described... I'm now trying to find any way to set the "App Name" from an Open connection, so that we can keep the connection string constant and do away with the pool creep nasties.
Tao
+5  A: 

I use SET CONTEXT_INFO for this. It's just what you need. Take a peek in BOL ..

Dave Markle
Aha, that looks just the ticket. The varbinary is a bit annoying but shouldn't be a big headache. Cheers.
Chris Simpson
Thank you! The great thing is: you can make it even work with concurrent direct database access, like from MS Access, and using the original connection's user name, by using something like this: COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
markus