Identifying a user - which variable to use? 1

[tadd]

Does anyone know if there is any real difference between the following?

USER
USER_NAME()
CURRENT_USER
SESSION_USER

If I execute the following code in Query Analyzer, I get the same value for each statement:

PRINT USER
PRINT USER_NAME()
PRINT CURRENT_USER
PRINT SESSION_USER

Why so many ways to get the same answer? There must be a difference between these...

 

[SQLSister]

Look in BOl it will exploain the differnces


Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[tadd]

What a concept! I never would have thought of that!

Actually, I was having trouble finding my answer in BOL, but I finally found it in an article entitled "Functions That Return User Names and User IDs." Here is a summary for anyone who is following this thread:

-----------------------------------------
In Transact-SQL, the following functions are implemented as synomyms for the USER_NAME() function, when USER_NAME() is not supplied a parameter:

USER
SESSION_USER
CURRENT_USER

Also:

"SQL-92 allows for SQL statements to be coded in SQL modules that can have authorization identifiers separate from the authorization identifier of the user that has connected to a SQL database. SQL-92 specifies that SESSION_USER always return the authorization identifier of the user that made the connection. CURRENT_USER returns the authorization identifier of the SQL module for any statements executed from a SQL module, or of the user that made the connection if the SQL statements were not executed from a SQL module. If the SQL module does not have a separate authorization identifier, SQL-92 specifies that CURRENT_USER return the same value as SESSION_USER. Microsoft SQL Server does not have separate authorization identifiers for SQL modules; therefore, CURRENT_USER and SESSION_USER are always the same. The USER function is defined by SQL-92 as a backward compatibility function for applications written to earlier versions of the standard. It is specified to return the same value as CURRENT_USER."

 

[SQLDenis]

you forgot one ;-) SUSER_SNAME()

SELECT SUSER_SNAME()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[tadd]

Yes, and it appears you can use SYSTEM_NAME as a synonym for SUSER_SNAME