I need to track some client program's query for index recommendations from Database Tuning advisor. For that I am using SQL server Profiler and exporting the table to Database Tuning Advisor. 

You might want to run the query below in your client's db
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

Hope that is what you are looking for...

AlwaysHappy,

Your query is not correct. @@SPID gives you the current SPID of the user. So You will give only one spid always.

Here is the complete query. 

IF EXISTS (SELECT * 
           FROM   tempdb.sys.objects 
           WHERE  object_id = Object_id('Tempdb.dbo.#cLIENTsPID')) 
  DROP TABLE #clientspid 

CREATE TABLE #clientspid ( 
  spid        VARCHAR(10), 
  [status]    VARCHAR(20), 
  [login]     VARCHAR(100), 
  [hostname]  VARCHAR(100), 
  blkby       VARCHAR(10), 
  dbname      VARCHAR(100), 
  command     VARCHAR(100), 
  cputime     INT, 
  diskio      INT, 
  lastbatch   VARCHAR(100), 
  programname VARCHAR(100), 
  spid1       INT, 
  requestid   INT) 

INSERT INTO #clientspid 
EXEC Sp_who2 

SELECT spid, 
       hostname, 

thanks guys.. will try this tomorrow.           FROM   tempdb.sys.objects 
           WHERE  object_id = Object_id('Tempdb.dbo.#cLIENTsPID')) 
  DROP TABLE #clientspid 

hareeb, If exists with condition makes sure that temp table is not created already. This is the best practice while creating any temp table. and Yes temp table is a session label variable. It will be destroyed when your session close. I dropped the table table because what if you need to run that command again and again (like with 10 minute interval). Write a extra few extra lines for code doesn't harm at all :). Well, you will soon understand the importance of if Exist when you write a big queries for production dbs and best of luck for that

thanks for the clarification virus. I am new to this, do you suggest any books, tutorials? I am currently looking at index tuning, fill factors etc.