Use sp_who2() to Get Current Log-in User Information

Recently I have been working on Microsoft SQL Server 2005 to resolve some security issues. One question was to get the current log in user information.

As usual, when I cannot find an answer in 10 minutes, I post my request to StackOverFlow web site. I posted my question early in a morning before I went to my work. Quickly I got several answers when I arrived to my office by biking. I tried all of them and found out sp_who2, a undocumented stored procedure by Microsoft SQL Server, is the tool to get information I need. By using this tool, actually I can find out who is using the SQL server anytime. For example, if a critical data refreshing or migration job is scheduled, I could add this tool to find out if there is any user access to SQL server and send out warning emails if any one there, before the job is about executing.

It is very easy to user SP:

EXEC sp_who2;

The SP returns a list of log in users in a table view if you use Microsoft SQL Server Management Studio's query. However, I only want to see related column information and filter users by WHERE and ORDER BY clauses. Then I found out a way to define a table and output the result to a variable table like this:

DECLARE @retTable TABLE (
  SPID int not null
  , Status varchar (255) not null
  , Login varchar (255) not null
  , HostName varchar (255) not null
  , BlkBy varchar(10) not null
  , DBName varchar (255) null
  , Command varchar (255) not null
  , CPUTime int not null
  , DiskIO int not null
  , LastBatch varchar (255) not null
  , ProgramName varchar (255) null
  , SPID2 int not null
  , REQUESTID INT
)  
INSERT INTO @retTable EXEC sp_who2  
SELECT Status, Login, HostName, DBName, Command,
  CPUTime, ProgramName, BlkBy AS [Last CMD Time] -- *
  FROM @retTable
  --WHERE Login not like 'sa%' -- if not intereted in sa
  ORDER BY Login, HostName;

To view all the user information, you need to login as sa or user with sa administrative privileges. Otherwise, you may only see yourself or limited information.