3

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.

1
  • Hadn't thought about the PID! Good idea.
    – Hotchips
    Sep 21, 2012 at 0:15

2 Answers 2

4

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).

8
  • 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, 2012 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. Sep 20, 2012 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, 2012 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. Sep 20, 2012 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)? Sep 20, 2012 at 3:01
3

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');
2
  • 1
    On modern versions of SQL Server you probably want to use sys.dm_exec_connections / sys.dm_exec_sessions ... Sep 21, 2012 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, 2012 at 22:12

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