MSSQL Active Users

Sometimes you want to be able to list the active users, and you can use the system stored procedures of sp_who and sp_who2 to do that. However, I've found that it's inconvenient for a couple of reasons.

1. If you run it from a command line, it scrolls over the 80 column limit you usually have in a terminal session (sometimes I run this using a command like osql -E -S sql -Q 'exec master..usp_ListUsers'). While this also goes over the 80 column limit, it goes over 2 lines, so it's more readable to me.
2. This allows you to enter either a database name or a user name and filter the output so you only see those results. In this way, I can see the users who are currently connected to master, msdb, or some other user database inside SQL Server. I just feel this gives me more flexibility in looking at what's going on with the server.

PRINT 'Building usp_ListUsers - Starting'
GO

IF NOT ( object_id ( 'usp_ListUsers' ) is NULL )
BEGIN
   DROP PROCEDURE usp_ListUsers
END
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SETUSER  'dbo'
GO

CREATE PROCEDURE usp_ListUsers
   @astr_database VARCHAR (50) = NULL,
   @astr_user VARCHAR (50) = NULL
AS
BEGIN

/*********************************************************************
NAME:  usp_ListUsers

DATABASE:
   master

PARAMETERS:
   astr_Database - (Optional) The database to report on (list users in)
   astr_User - (Optional) The user to find and report on

ASSUMPTIONS:
   NONE

COMMENTS:
   NONE

AUTHOR(S):
   TJB - Timothy J. Bruce

PURPOSE:

MODIFICATION HISTORY

Init  Date      Comments
TJB   07Mar02   Initially Created

*********************************************************************/

/* Declare Variables */

   DECLARE @lstr_Database CHAR ( 20 )
   DECLARE @lstr_Error1 CHAR ( 50 )
   DECLARE @lstr_Error2 CHAR ( 50 )
   DECLARE @lstr_user VARCHAR ( 50 )

   DECLARE @llng_Count INTEGER
   DECLARE @llng_DBID INTEGER

/* Set up the Environment */

   SET NOCOUNT ON

/* Begin Processing */

   IF @astr_database IS NOT NULL
   BEGIN
       IF db_id ( @astr_database ) IS NULL
       BEGIN
       -- If there is no associated db_id, it doesn't exist.
            SELECT @lstr_Error1 = 'The database name you entered ('
            SELECT @lstr_Error2 = ') is not valid for this server.'
            SELECT 'Error'=@lstr_Error1 + @astr_database + @lstr_Error2
            RETURN ( -1 )
       END
       ELSE
       BEGIN
       -- Found the database - lets list all the users in it.
           --SET 'DB' = @astr_database
           SELECT
               spid,
               'Status' = CONVERT ( CHAR(10), status ),
               'login' = CONVERT ( CHAR (15), loginame),
               'NT Account' = CONVERT ( CHAR (12), nt_username),
               'Workstation Id' = CONVERT ( CHAR (15), hostname),
               blk = CONVERT ( CHAR (5), blocked),
               dbname = CONVERT ( CHAR (20), db_name(dbid) ),
               'Program' = CONVERT ( CHAR (10), program_name),
               'Command' = CONVERT ( CHAR (20), cmd )
           FROM
               master.dbo.sysprocesses
           WHERE
               spid BETWEEN 0 and 32767 AND
               dbid = db_id ( @astr_database )
           ORDER BY
               loginame,
               hostname,
               spid
       END
   END
   ELSE
   BEGIN
       IF @astr_user IS NOT NULL
       BEGIN
           SELECT @lstr_user = '%'+ @astr_User + '%'
           SELECT @llng_Count = COUNT(*) FROM master..sysprocesses WHERE UPPER ( nt_username ) LIKE UPPER ( @lstr_user )
           IF @llng_Count  = 0
           BEGIN
               SELECT 'Error' = 'User ''' + @astr_User + ''' could not be found.'
               RETURN ( -2 )
           END
           ELSE
           BEGIN
               SELECT
                   spid,
                   'Status' = CONVERT ( CHAR(10), status ),
                   'login' = CONVERT ( CHAR (15), loginame),
                   'NT Account' = CONVERT ( CHAR (12), nt_username),
                   'Workstation Id' = CONVERT ( CHAR (15), hostname),
                   blk = CONVERT ( CHAR (5), blocked),
                   dbname = CONVERT ( CHAR (20), db_name(dbid) ),
                   'Program' = CONVERT ( CHAR (10), program_name),
                   'Command' = CONVERT ( CHAR (20), cmd )
               FROM
                   master.dbo.sysprocesses
               WHERE
                   spid BETWEEN 0 and 32767 AND
                   UPPER ( nt_username ) LIKE UPPER ( @lstr_user )
               ORDER BY
                   loginame,
                   hostname,
                   spid
           END
       END
       ELSE
       BEGIN
           /* All Users & Processes */
           SELECT
               spid,
               'Status' = CONVERT ( CHAR(10), status ),
               'login' = CONVERT ( CHAR (15), loginame),
               'NT Account' = CONVERT ( CHAR (12), nt_username),
               'Workstation Id' = CONVERT ( CHAR (15), hostname),
               blk = CONVERT ( CHAR (5), blocked),
               dbname = CONVERT ( CHAR (20), db_name(dbid) ),
               'Program' = CONVERT ( CHAR (10), program_name),
               'Command' = CONVERT ( CHAR (20), cmd )
           from
               master.dbo.sysprocesses
           where
               spid BETWEEN 0 and 32767
           ORDER BY
               loginame,
               hostname,
               spid
       END
   END
   RETURN ( 0 )
END

GRANT EXECUTE ON usp_ListUsers TO PUBLIC

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SETUSER
go

PRINT 'Building usp_ListUsers - Finished'
GO

If you have any questions, feel free to contact me.