Get Logged-in User Session Details

-- ============================================================================
-- Get Logged-in User Session Details
--
-- A cursor that loops through each session ID currently active on the SQL
-- Server instance. The block executes "SP_WHOon" each session to see each
-- session's logged-in user name and other data.
-- ============================================================================

--
-- Do not show rowcounts in the results
set nocount on;

declare @session_id smallint;

--
-- Declare the cursor
declare session_cursor cursor forward_only read_only
for select session_id
    from sys.dm_exec_requests
    where status in ('runnable', 'sleeping', 'running');

--
-- Open the cursor
open session_cursor;

--
-- Retrieve one row at a time from the cursor
fetch next from session_cursor into @session_id;

--
-- Process and retrieve new rows until no more are available
while @@FETCH_STATUS = 0
    begin
        print 'Spid #: '+STR(@session_id);
        exec ('sp_who '+@session_id);

        fetch next from session_cursor into @session_id;
    end;

--
-- Close the cursor
close session_cursor;

--
-- Deallocate the cursor
deallocate session_cursor;