CODEWITHSUNDEEP
sql-servert-sql
Shurmajee
Shurmajee

Reputation: 1047

What is the difference between SYSTEM_USER and USER in SQL server?

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?

Upvotes: 6

Views: 12712

Answers (3)

Eddie Kumar
Eddie Kumar

Reputation: 1488

  • 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().

Upvotes: 9

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

According to difference between SYSTEM_USER and USER:

If the current user is logged in to Microsoft® SQL ServerT using Windows Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login identification name

for example, DOMAIN\user_login_name.

However, if the current user is logged in to SQL Server using SQL Server Authentication, SYSTEM_USER returns the SQL Server login identification name,

for example, sa for a user logged in as sa

Upvotes: 0

Boopathi Rajan
Boopathi Rajan

Reputation: 1210

SYSTEM_USER to return the current system user name.

USER to return the database user name.

Upvotes: 1

Related Questions