1

In SQL Server I've got the session id of a process that is blocking other processes. Now I'd like to identify the specific user or machine that causes this blocking. The users are authenticating using SQL Server Authentication.

Normally I can get the desired information from the activity monitor (or through a query). But in case that the user connected from a Remote Desktop session, I'm lost. The host name is same for all RDP users and there is no additional information that helps distinguishing the sessions. It seems there is no way to actually identify the user who is actually blocking the others.

Is there any way to identify the user nevertheless? If yes, how?

(If it's not possible, then I have to live with it...)

Improve this question
4
  • So--is the SQL login that the user is using being shared by other users?
    – Tony Hinkle
    Commented Jun 3, 2019 at 14:54
  • There's a couple ways to go with this question. But "blaming" a user for running a blocking query is probably coming at the problem from the wrong end of the ice-cream cone. Any reason you can't just kill blocking queries as-and-when you need to?
    – Peter Vandivier
    Commented Jun 3, 2019 at 15:01
  • @Tony Hinkle: Yes, the SQL login is being shared by other users.
    – MicSim
    Commented Jun 3, 2019 at 15:45
  • @Peter Vandivier: I'm not going to blame the user, but want to know, what they did, so the blocking took place. The blocking in this special case is an absolute exception. So I could have killed the query and go on (which in effect I did, after being unable to identify the user).
    – MicSim
    Commented Jun 3, 2019 at 15:48

1 Answer 1

Reset to default
2

One way to accomplish this would be to use the host_process_id column from sys.dm_exec_sessions

This host_process_id in this table maps to the PID in task manager.

In this case we could run the following query to get our host_process_id, and then take the result and check in task manager which application currently has a PID matching the result.

SELECT session_id, host_process_id FROM sys.dm_exec_sessions WHERE session_id = XXX

You can then check which account is running that process.

Improve this answer
6
  • You can use this value to examine the local process and get the owner via the following Powershell 1-liner (gwmi win32_process | ? processid -eq $host_process_id).GetOwner().user
    – Peter Vandivier
    Commented Jun 3, 2019 at 15:25
  • 1
    Please note that these values do not need to be filled in and can be set to various values depending on how the application and driver is written. This also doesn't take into account middle-tiers that you won't be able to track back further.
    – Sean Gallardy
    Commented Jun 3, 2019 at 15:28
  • @SeanGallardy - OP specifies the session appears to be RDP/local. If the host_name is spoofed and the connection is not local, will the host_process_id be null, reported from the connection (and spoofable), or something else?
    – Peter Vandivier
    Commented Jun 3, 2019 at 15:38
  • To clarify: The SQL Server and the terminal server where the RDP session runs are 2 different servers. So the user connection is from a remote machine.
    – MicSim
    Commented Jun 3, 2019 at 15:53
  • @PeterVandivier depends on what the application and driver are set to do. Most/All of the values can be spoofed, not set, set, or any combinations of the previous. While it's a good place to start, it's not a definitive source, I was just putting the disclaimer that it may not yield anything because it's not enforced to be filled in or correct.
    – Sean Gallardy
    Commented Jun 3, 2019 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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