Cry How to...

Determine the name of the user for the current session

SQL Server provides a number of ways of getting information about the current user:

• sp_who @@SPID

will give the user name and other session information about the current user session.

• The database variable 'SYSTEM_USER' returns the domain and login name of the user IF windows authentication is used. However if current user is logged on using SQL Server Authentication then 'SYSTEM_USER' returns the SQL Server login identification name. For example:

  print SYSTEM_USER

  might yield 'PRO_BRAIN\Brian Cryer', if the user 'Brian Cryer' were logged on using NT authentication from a workstation called 'PRO_BRAIN' (note 'XP' allows spaces in the user name whilst NT does not)
  or 'sa' if connected using the 'sa' account using SQL Server Authentication.

• The database variable 'CURRENT_USER' (or 'SESSION_USER' - the two always give the same value) returns the database authorization name of the user that made the connection.

• Other information can be found by interrogating the table 'master..sysprocesses' directly, for example:

select nt_username, hostname, nt_domain, loginame, login_time, program_name from master..sysprocesses where spid=@@SPID

These notes have been tested against SQL Server 7 and SQL Server 2000.