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 · Sep 29, 2010 5:16 PM

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,        ProgramName FROM   #clientspid  WHERE  hostname NOT IN ('  .',@@SERVERNAME) Last edited: 29-Sep-10 05:26 PM

virusno1 · Sep 29, 2010 5:22 PM

thanks guys.. will try this tomorrow. virus,a quesitron for you.. IF EXISTS (SELECT *             FROM   tempdb.sys.objects            WHERE  object_id = Object_id('Tempdb.dbo.#cLIENTsPID'))   DROP TABLE #clientspid  what does this do? Since we have '#', this means the table #cLIENTsPID will be dropped automatically once the session is over right? so why do we need to drop it. also what does this if exists section do. if i understand it correctly, your script just creates a table from sp_who2. Please help me understand. Your help is appreciated. Sorry I am new to DBA. thanks

the_hareeb · Sep 29, 2010 11:16 PM

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

virusno1 · Sep 30, 2010 10:39 AM

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.  

the_hareeb · Sep 30, 2010 1:27 PM