How to know the original user name

Question

How to know the original user name

Biank

Ten Centuries

Points: 1332
More actions
April 17, 2013 at 5:42 pm

#276511

Hi all,
I'm having hard time to figure out a way to create a DDL trigger while capturing the original user name who initiated a connection. ASP.NET application is running on IIS server and AppPool is configured with a shared account to make a connection to SQL. So, from the SQL server side we only see this shared account.
My problem is, how to know who actually initiated the connection originally. ORIGINAL_LOGIN() won't work since IIS is using a shared account so wonder if there's a way from SQL side.
Anyone knows if this is even possible? I don't think it is but wonder if there's a way, hope...
Thanks~

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

I could be wrong but I don't believe it's possible unless the app captures the login and somehow passes it along. SET CONTEXT_INFO may prove useful in this area because it's good for the scope of the session.

Do a search for "Using Session Context Information" in Books Online (ad, perhaps, on the Web) for more information. Here's a the first hit I got on Google for the phrase.

http://msdn.microsoft.com/en-us/library/ms189252(v=sql.105).aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Biank Ten Centuries Points: 1332 More actions · Answer 2

Thanks Jeff.

It seems like there's a way if we use CONTEXT_INFO() function, I'll do more research on it.

Thanks again~

jay81 Old Hand Points: 381 More actions · Answer 3

You can use SYSTEM_USER function in SQL Server. Please refer to the below link.

http://msdn.microsoft.com/en-us/library/ms179930.aspx

Biank Ten Centuries Points: 1332 More actions · Answer 4

Thanks jay81 but SYSTEM_USER won't work since it'll return current login info but we're using one shared account for all users.

As Jeff suggested, CONTEXT_INFO() might work that I'm playing around now.