Find User accessing SQL Server from Application

Question

Find User accessing SQL Server from Application

CrazyDBA

Old Hand

Points: 356
More actions
January 8, 2009 at 5:00 am

#126342

Hi,
It would be very useful to know which user is accessing sql server and executing what sql statements specially when resolving locking issues.
I have noted that our applcation is connecting to sql server using login instead of application role so activity monitor or dmv will show that login(i.e.TestApplication) as a user( if 10 users are logged in to application there will be 10 sessions called TestApplication). is there anyway i can findout which user is accessing sql server through TestApplication login from application?

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

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

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

You should also see the machine name (hostname) associated with the login. As long as they do not remote into a generic server, having the machine name you should be able to track down which user it is causing the problem. If you do not have the information for yourself as to who owns what machine you may have to talk to your Network Admin staff but it should still be feasible.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

The hostname as previously suggested would be the only way I can think of to come up with this without changing your app code to track the users information in the connection context_info. If this is a web application, then the only way you could do it is to set the context info and then read it back.

To set it:

DECLARE @ContextInfo varbinary(128)

SELECT @ContextInfo = cast( 'This is a Test' AS varbinary(128) )

SET CONTEXT_INFO @ContextInfo

Then to read it back:

SELECT cast( CONTEXT_INFO() as varchar(128) )

CrazyDBA Old Hand Points: 356 More actions · Answer 3

Host name will not work as it will give me a name of machine where application is installed.

well changing code is another problem as it is handle by third party. Any more ideas?

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 4

CrazyDBA (1/8/2009)
Host name will not work as it will give me a name of machine where application is installed.
well changing code is another problem as it is handle by third party. Any more ideas?

Generic user...generic host...can't change code. That pretty much covers everything right there that I would know about.

You could always send out an email to everyone in the company...but I am sure you don't want to go that route.