Get .NET user ID from SQL Server trigger

[d0wsdkn02]

I have an ASP.NET application that performs ADO.NET commands that cause
triggers to be run in SQL Server 2000.

My question is how would I best access the user ID from my custom .NET
Principal in the trigger (the trigger stores audit information and
needs the user ID)?

I'm using the same SQL Server user/pass on every connection to
facilitate connection pooling, so it's not available that way.

I've seen references to using SQL Server's CONTEXT_INFO or creating a
table to hold session data. I could also do something like this:

"set @userid='bob'; insert into customer values (1,2,3);"

but I'm not sure how to access that userid variable from the trigger
that will be fired.

Any advice would be appreciated. Thanks!

 

[Guest]

Where is the trigger? On the user table? If so, make sure the trigger is on
insert and look at the inserted value. If you are on another table, this is
problematic and you will need to find the information from the user table,
which may or may not be possible.

WIthout more information, I can only shoot in the dark.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Bruce Barker]

connection pooling makes this difficult. the usual trick is a table with
userid by spid, or a conntection setting (like hostname).

i've switched to all procs requiring user login be passed (so they can
validate access and get the userid), and adding lastmodifiedby and
lastmodifieddate to all tables. then the triigers have access.

-- bruce (sqlwork.com)

 

[d0wsdkn02]

In my example the trigger would be on the customer table. There might
not even be a user table. 'bob' is the information I need to get at
(which isn't stored in the customer table (unless I add it for just
this purpose)).

 

[d0wsdkn02]

Thanks for the reply.

I don't really like making the session table. I guess I'll have to add
lastmodifiedby to the tables to get it. How would you get the user on
a delete trigger? I guess it would have to be one of the other
methods. Hmm..

Yeah, it would be easier without connection pooling.