Brian Cryer

 

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.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.