SQL Server Performance Forum – Threads Archive

Finding username from SPID in SQL2000

We have a problem with row locking in our databases. The database is aprt of our ERP setup and has a single access username and password. Logon to the ERP front end is via Windows authentication and is seperate from the SQL logon. When we get a lock problem on the SQL database, the only information we can get is the SPID’s of the users locking each other. We then need to query the list of Processes running on the sewrver and identfy the user from matching the SPID from SQL with the PID on the server. Is there anyway that I can pick out the username from within SQL? We are running Windows 2000 SP4/SQL2000 SP4 I might add that we were able to get the username when we were running SQL7. So what has changed? regards Jimbob91

---

Try:
select loginame from master.dbo.sysprocesses where spid = X

---

I was also caught in the same situation and found NO way to handle this requirement and worked manually to find out user names.<br /><br />I have a stored proc, which can: – (Not written by me <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />Print out DBCC Input buffer for all blocked or blocking spids.<br />Print out DBCC PSS info only for SPIDs at the head of blocking chains.<br />Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.<br /><br /><br />I hope other forum user’s can help you, if you need above SP.. Please let me know.<br /><br /><br /><br />Deepak Kumar<br /><br />–An eye for an eye and everyone shall be blind

---

Deepak, please post that in the forums area to "contribute your scripts". MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

You can run sp_who or sp_who2
Madhivanan Failing to plan is Planning to fail

---

Derrickleggett: This SP is not written by me.. still, can I post? [?] Please clarify !
Deepak Kumar –An eye for an eye and everyone shall be blind

---

Deepak, you can post it if the author has no objection
Madhivanan Failing to plan is Planning to fail

---

http://support.microsoft.com/default.aspx?scid=kb;en-us;271509 I found that above link has got similar stored proc.. but code (SP) I was talking about is little different like: – –use master
–GO
–SQL2K if exists (select * from sysobjects where id = object_id(‘dbo.sp_blocker_pss80’) and sysstat & 0xf = 4)
drop procedure dbo.sp_blocker_pss80
GO create proc sp_blocker_pss80 (@fast int = 0)
as
set nocount on
declare @spid varchar(6), @blocked varchar(6)
declare @tmpchar varchar(255)
declare @time datetime select @time = getdate() declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid from sysprocesses where blocked!=0 or waittype != 0x0000
if exists (select spid from @probclients)
begin
select @tmpchar=’Start time: ‘ + convert(varchar(26), @time, 113)
print @tmpchar insert @probclients select distinct blocked, 0, 0, 0x0000, 0 from @probclients
where blocked not in (select spid from @probclients) and blocked != 0
print ‘ ‘
if (@fast = 1)
begin
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage,last_batch=convert(varchar(26), last_batch,113),
login_time=convert(varchar(26), login_time,113), net_address,
net_library,dbid, ecid, kpid, hostname,hostprocess,
loginame,program_name, nt_domain, nt_username, uid, sid,
‘sprocpss80’, runtime = convert(varchar(26), @time, 113)
from master..sysprocesses
where blocked!=0 or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0)
or spid in (select spid from @probclients where blocked != 0) select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then ‘NUL’
when 2 then ‘DB’
when 3 then ‘FIL’
when 4 then ‘IDX’
when 5 then ‘TAB’
when 6 then ‘PAG’
when 7 then ‘KEY’
when 8 then ‘EXT’
when 9 then ‘RID’
when 10 then ‘APP’ end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then ‘Sch-S’
when 3 then ‘Sch-M’
when 4 then ‘S’
when 5 then ‘U’
when 6 then ‘X’
when 7 then ‘IS’
when 8 then ‘IU’
when 9 then ‘IX’
when 10 then ‘SIU’
when 11 then ‘SIX’
when 12 then ‘UIX’
when 13 then ‘BU’
when 14 then ‘RangeS-S’
when 15 then ‘RangeS-U’
when 16 then ‘RangeIn-Null’
when 17 then ‘RangeIn-S’
when 18 then ‘RangeIn-U’
when 19 then ‘RangeIn-X’
when 20 then ‘RangeX-S’
when 21 then ‘RangeX-U’
when 22 then ‘RangeX-X’end,
Status = case req_status when 1 then ‘GRANT’
when 2 then ‘CNVT’
when 3 then ‘WAIT’ end,
req_transactionID As TransID, req_transactionUOW As TransUOW,
‘slockpss80’, runtime = convert(varchar(26), @time, 113)
from master.dbo.syslockinfo s,
@probclients p
where p.spid = s.req_spid
end — fast set else
begin — Fast not set
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage,last_batch=convert(varchar(26), last_batch,113),
login_time=convert(varchar(26), login_time,113), net_address,
net_library,dbid, ecid, kpid, hostname,hostprocess,
loginame,program_name, nt_domain, nt_username, uid, sid,
‘sprocpss80’, runtime = convert(varchar(26), @time, 113)
from master..sysprocesses print ”
print ‘SPIDs at the head of blocking chains’
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
print ” select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then ‘NUL’
when 2 then ‘DB’
when 3 then ‘FIL’
when 4 then ‘IDX’
when 5 then ‘TAB’
when 6 then ‘PAG’
when 7 then ‘KEY’
when 8 then ‘EXT’
when 9 then ‘RID’
when 10 then ‘APP’ end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then ‘Sch-S’
when 3 then ‘Sch-M’
when 4 then ‘S’
when 5 then ‘U’
when 6 then ‘X’
when 7 then ‘IS’
when 8 then ‘IU’
when 9 then ‘IX’
when 10 then ‘SIU’
when 11 then ‘SIX’
when 12 then ‘UIX’
when 13 then ‘BU’
when 14 then ‘RangeS-S’
when 15 then ‘RangeS-U’
when 16 then ‘RangeIn-Null’
when 17 then ‘RangeIn-S’
when 18 then ‘RangeIn-U’
when 19 then ‘RangeIn-X’
when 20 then ‘RangeX-S’
when 21 then ‘RangeX-U’
when 22 then ‘RangeX-X’end,
Status = case req_status when 1 then ‘GRANT’
when 2 then ‘CNVT’
when 3 then ‘WAIT’ end,
req_transactionID As TransID, req_transactionUOW As TransUOW,
‘slockpss80’, runtime = convert(varchar(26), @time, 113)
from master.dbo.syslockinfo
end — Fast not set dbcc traceon(3604)
Print ”
Print ”
Print ‘*********************************************************************’
Print ‘Print out DBCC Input buffer for all blocked or blocking spids.’
Print ‘Print out DBCC PSS info only for SPIDs at the head of blocking chains’
Print ‘*********************************************************************’ declare ibuffer cursor fast_forward for
select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
from @probclients
where (spid <> @@spid) and (blocked!=0
–Remove comment on following line to see DBCC INPUTBUFFER and PSS for spids not involved in blocking via locks (ie latches)
–or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid, @blocked
while (@@fetch_status != -1)
begin
print ”
print ”
exec (‘print ”DBCC INPUTBUFFER FOR SPID ‘ + @spid + ””)
exec (‘dbcc inputbuffer (‘ + @spid + ‘)’) print ”
if (@blocked = ‘0’)
— if DBCC PSS is not required, comment the line above, remove the
— comment on the line below and run the stored procedure in fast
— mode
— if (@blocked = ‘0’ and @fast = 0)
begin
exec (‘print ”DBCC PSS FOR SPID ‘ + @spid + ””)
exec (‘dbcc pss (0, ‘ + @spid +’)’) print ”
print ”
end
fetch next from ibuffer into @spid, @blocked
end
deallocate ibuffer Print ”
Print ”
Print ‘*******************************************************************************’
Print ‘Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.’
Print ‘*******************************************************************************’
declare ibuffer cursor fast_forward for
select distinct cast (dbid as varchar(6)) from @probclients
where dbid != 0
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ”
print ”
exec (‘print ”DBCC OPENTRAN FOR DBID ‘ + @spid + ””)
exec (‘dbcc opentran (‘ + @spid + ‘)’)
print ”
if @spid = ‘2’ select @blocked = ‘Y’
fetch next from ibuffer into @spid
end
deallocate ibuffer
if @blocked != ‘Y’
begin
print ”
print ”
exec (‘print ”DBCC OPENTRAN FOR tempdb database”’)
exec (‘dbcc opentran (tempdb)’)
end
if datediff(millisecond, @time, getdate()) > 1000
begin
select @tmpchar=’End time: ‘ + convert(varchar(26), getdate(), 113)
print @tmpchar
end dbcc traceoff(3604)
end — All
GO
Deepak Kumar –An eye for an eye and everyone shall be blind

---

Unfortunately this does not give correct information – login name is same name as Dbase name

---

Deepak Not quite sure what the Procedure is doing as yet, but next time I am asked who is causing a block, I will run it, just to see what it gets. The other thing we were trying to do was to run a procedure which added data to a table so that we can do some statistical work. To all the others who replied, sp_who, etc. won’t work in this situation, since the logon name and the database name are identical. i.e. Dbase name is fin59, logon name is fin59 and password is fin59. This a requirement of our ERP vendor to make their software work. regards jimbob91 [?]

---

Jim : I already mentioned that.. I am not aware how to get windows userName who own the PID, even me too worked manually to get information.<br /><br />I have full sympaty with you that you are working (like i worked) on a pretty decent software [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><br />You can alter above code to store results in tables, for statistical usage.<br /><br />Deepak Kumar<br /><br />–An eye for an eye and everyone shall be blind

---

Jim, if you want to know the system user, you can use select system_user
Madhivanan Failing to plan is Planning to fail

---

No comment on that part… You might be able to run a program like tlist.exe or tasklist.exe and dump out that data into a file, import the data in sql and do some queries on it.

---

May try catching the MacAddress from Enterprise Manager –> Management –> Current Activity –> Processess for relevant user name. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>