DBMS_SESSION.set_client_identifier equivalent in SQL Server

I'd like to know if there's a way to set client identifier context like in Oracle by using DBMS_SESSION.set_client_identifier and grab the value using a function.

I am trying to track data changes including who/which username made the changes. My plan to create Triggers that capture the OLD value, NEW value, and the username.

The connection to the serv开发者_运维问答er is using pooled connection, so using the username that connects to the DB is not the answer.

Anyone has an idea?

---

If you cannot use properties from the connection itself (since it's pooled) you will have to set some additional info after your call to connection.Open(). But if I read your question correctly, that's OK for you.

So what you can use is SQL Servers SET CONTEXT_INFO, which lets you store up to 128 arbitrary bytes in the session/connection context and which you can query thereafter with the CONTEXT_INFO() function. See the description >here<.