Finding nt_username from hostprocess in sysprocesses
-
Looking through the various forums, I can see that nt_username will not be populated within sysprocesses on SQL 2000 by a process using sql authentication. However, we have a problem where occasionally the application accessing a particular database locks up. We have to identify the user who has created the lock and get them to go on to the next process or kill the user's application.
What I would like to do is to find the username from the hostprocess in sysprocesses. Currently, I can identify the locking SPID and hostprocess, then I have to remotely log on to the SQL server and use task manager to identify the login name. If I could identify the login name, we could then resurrect our ASP.net process which would allow end users to identify who is locking them out.
Anyone got any ideas?
regards
Jim Draper
-
I'm a bit confused as to what the situation is. Do you have a user that is using a Web frontend via their NT account, and then the Web frontend makes its connection to SQL Server via a SQL Server login? And you are wanting to figure out which NT Account is running the offending thread? Or, do you have a user logging into a Web frontend via their SQL Server login (which is passed to SQL Server), and you are wanting to find their associated NT Account name? Would be helpful if we had a bit more architecture explained.
For instance, we use SQL Logins on some applications, and we don't even get the hostname passed to sysprocesses, so we have to use the MAC address that's in sysprocesses against a Cisco database in order to figure out who's causing us grief. But if the hostname is the IIS Server, the MAC address wouldn't help to identify where it was coming from.
-
This particular application is using a 4GL language to access the data in the database. We currently don't use our intranet for anything other than queries and all use nolock. The problem occurs when one user is trying to update a record which is locked by someone else and the application just hangs with no indication on who is locking whom. Under SQL7, the username was within sysprocesses, thus making it easy to write an ASP front end so that users could log into our Intranet and find out who was locking them out. Now evene we have difficulty in finding out who is doing the locking.
We don't use SQL logins as such ( the application logs in but that is all), all the authentication is done via Windows authentication.
Does that help
regards
Jim
-
Hi
I've experienced a similar problem with one of our applications where the connection is made by the application using a single sql login for all users. My initial investigation have found that sysprocesses will capture the nt_username if the client is connecting via named pipes and NOT TCP/IP. I don't no why yet but i'm still looking!
Hope this helps
regards,
Gary
-
Unfortunately, we cann't use named pipes either due to the restrictions placed on us by the 4GL software providor.
regards
Jim:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply