By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,602 Members | 1,507 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,602 IT Pros & Developers. It's quick & easy.

Get .NET user ID from SQL Server trigger

P: n/a
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!

Feb 23 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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!
***************************
"d0*******@sneakemail.com" wrote:
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!

Feb 23 '06 #2

P: n/a
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)


<d0*******@sneakemail.com> wrote in message
news:11*********************@t39g2000cwt.googlegro ups.com...
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!

Feb 23 '06 #3

P: n/a
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)).

Feb 23 '06 #4

P: n/a
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.

Feb 23 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.