Simple script to list out SPID, Program_name and login details

select spid,dbid,status from sys.sysprocesses
where spid> 50

exec find_session @dbid = 60

use Tempdb
go
alter procedure find_session (@dbid int)
as
declare @spid int
declare @sql nvarchar(max)
declare @spidcnt int
declare @i int=0

CREATE TABLE #get_SPID
(
      id int identity(1,1),
      SPID INT,
      app_program_name varchar(300),
      loginame varchar(200),
      inputbuf varchar(4000) null
)

INSERT INTO #get_SPID (SPID, app_program_name, loginame)
select spid,[program_name],loginame from sys.sysprocesses where dbid = @dbid

SELECT @spidcnt = count(*) FROM #get_SPID

WHILE (@i < @spidcnt)
BEGIN
DECLARE @output_tbl TABLE (EventType varchar(200), Params int, EventInfo varchar(4000))
select @spid = spid from #get_SPID where id = @i
    PRINT 'DBCC Results for SPID ' + Cast(@spid as varchar(5)) 
    PRINT '-----------------------------------'
    PRINT ''
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@spid AS NVARCHAR) + ')'
INSERT INTO @output_tbl EXEC (@sql)
UPDATE #get_SPID set inputbuf = (SELECT EventInfo FROM @output_tbl) WHERE id = @i
DELETE FROM @output_tbl
SET @i+=1
END

SELECT * FROM #get_SPID
go