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: Brian Cryer 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.