Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Possible Duplicate:
ProcessID Owner with third party app

Several of our applications use the sa account to login to our SQL Server 2008 instance. As these applications are poorly designed enough to use sa as the login, so are they poorly designed enough to use the .Net SQLQuery Data Provider for their application name.

Our system is also thin-client-ish. In essence, all requests show up from the same host, that most users RDP into.

Recently, one of them has been forcing the database server to 90% CPU for up to 15 minutes at a time. But, I can't figure out who is doing what to cause it.

So: is there a way for me to tell which Windows user the application that is logging in as sa is running under? If yes, I can just ask them what they're doing.

share|improve this question
Hadn't thought about the PID! Good idea. – Hotchips Sep 21 '12 at 0:15

marked as duplicate by Jon Seigel, Derek Downey, Phil, Nick Chammas, Paul White Oct 21 '12 at 22:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

up vote 3 down vote

If you can identify the query that is causing the problem (e.g. via session_id in sys.dm_exec_requests), you should be able to resolve the host_name by checking the same session_id in sys.dm_exec_connections. In lieu of an actual Windows account name, you should be able to tell who it is by the host name they're connecting from (assuming they aren't all using Remote Desktop and running queries locally too).

share|improve this answer
Ah, you've just reminded me. All users RDP in to the same host, so host name doesn't help either. x_x – Hotchips Sep 20 '12 at 2:01
2  
Well sounds like if you want to trace who is doing what, you need to stop providing ways to enable them to essentially be anonymous. – Aaron Bertrand Sep 20 '12 at 2:03
Oh what I wouldn't give. This organisation hasn't exactly been IT driven in the past. Now that they're starting to see the benefits (they've employed me, that's a start I guess), there's too much momentum behind the system set up by non-IT people and disinterested consultants. – Hotchips Sep 20 '12 at 2:07
2  
If you change the sa password (or create a logon trigger that only allows specific applications), I bet they come pretty quickly asking you to create individual SQL logins for them or turning their Windows auth logons on. – Aaron Bertrand Sep 20 '12 at 2:13
1  
@Hotchips they'd have a hard time justifying any pitchforks and torches. Do you answer to them, or do you both answer to a higher power? Do you suspect the higher power will understand the need for accountability, or do you think they will favor your co-workers' only benefit to working this way (laziness)? – Aaron Bertrand Sep 20 '12 at 3:01
show 3 more comments
up vote 1 down vote

Once you have things cleaned up as Aaron mentions, you might consider adding the following query to an SNMP monitoring system and trigger an alarm if this changes from 0

SELECT COUNT(*) 
FROM sys.dm_exec_sessions 
WHERE (login_name = 'sa' AND host_name <> '') 
    OR (login_name = 'mydomain\domain_sa');
share|improve this answer
1  
On modern versions of SQL Server you probably want to use sys.dm_exec_connections / sys.dm_exec_sessions ... – Aaron Bertrand Sep 21 '12 at 1:29
Great point Aaron, and sys.dm_exec_sessions is four times faster than master..sysprocesses (19 msec instead of 77 msec), I've edited the snmp trap query above. – kkarns Sep 21 '12 at 22:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.