SQL Database sessionId

Question

Is there a sessionId in SQL Database similar to the sessionId in asp.net?

If there is one could any one explain how to get it.

 

I'd like to accomplish the following, I have a sql database with one single dbo owner user which I use to login with my asp.net app but I am auditing (ADD, DELETE, UPDATE) some tables and I would like to do the audting with the user that logged on the app.

 

Explain:

The Sql Database has the users table tblUsers(UserId, Password, DateCreated,ActiveUser) 

there is a trigger in the products table and in the purchase order table as of now the trigger works fine but it always audting with the dbo user {USER_NAME()} what I'd like to accomplish is that the userId from the table users (tblUsers->UserId) be captured in the audit instead of the dbo user assigned to the database.

 

How can I set the USER_NAME() = TblUsers->UserId value or the SUSER_NAME()= TblUsers->UserId Value in order to capture the proper logged on userId.

 

In my auditing table, the trigger is capturing the value of the USER_NAME(). 

 

Thanks