Passing extended parameter into Sql 2008 connection string

Question

Hi, I have a need to support extensive auditing capabilities for a system backing into Sql Server 2008. Since I plan to use LINQ (with no Stored Procs), the database would be a clean, zero contact data repository.

However, I need to pratically record a snapshot of every change that happens in the db. So, I thought I should use triggers. But then, I need a user id for the particular user (not the connection string user id) to flow through into the database.

In oracle, I should have been able to set up a PROXY USER and the trigger would be able to pick that up. Last I checked, there was no proxy user concept in Sql Server.

Does anyone know if there's any extender property I can use to flow through my authenticated user name?

ps: I don't mind the impact on connection pooling (if any).

Thanks. P

Answer 1

I solved this problem by associating the @@SPID with the current User ID.

You need a table with the structure SPID int, UserID int, and an SP with the same parameters. The SP deletes the record with the SPID if it exists, and inserts the new pair.

In the trigger, you can retrieve the User ID by querying this table with @@SPID.

The SP needs to be called whenever a connection is opened. (you did not mention how you want to access the db)

Answer 2

I haven't tried this, but from the parameters in a connection string, these two (probably more) can be retrieved in T-SQL: APP_NAME() and HOST_NAME().