I am very new to databases and confused between the keywords SYSTEM_USER and USER in MSSQL. The definitions say that they return the username in the current context. but the instance I have running returns 'dbo' for USER and 'sa' for system user.
Can somebody highlight the exact difference between the two?
SYSTEM_USER: returns Server Login name that was used to login to the instance (either SQL Server login or AD/Domain/Windows user-name).USER, CURRENT_USER, USER_NAME(), or SESSION_USER: these all return Database User principal, which is (by default) dbo if you are db-owner or logged in as a sysadmin or sa (not to be confused with the dbo schema or the DB_Owner of current database in use).Examples:
SELECT SYSTEM_USER --> myDomain\user.name
----------------------------------------------
SELECT USER --> dbo
SELECT CURRENT_USER --> dbo
SELECT USER_NAME() --> dbo
SELECT SESSION_USER --> dbo
----------------------------------------------
Note: USER_NAME([user_id]) can additionally take an int-user-id, default arg is 1, i.e.: USER_NAME(1) would be same as USER_NAME().