Transact-SQL Reference

SYSTEM_USER

Allows a system-supplied value for the current system username to be inserted into a table when no default value is specified.

Syntax

SYSTEM_USER

Remarks

Use the SYSTEM_USER niladic function with DEFAULT constraints in either the CREATE TABLE or ALTER TABLE statements, or use as any standard function.

If the current user is logged in to Microsoft® SQL Server™ 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.

Examples
A. Use SYSTEM_USER to return the current system username

This example declares a char variable, puts the current value of SYSTEM_USER into the variable, and then prints the variable.

DECLARE @sys_usr char(30)
SET @sys_usr = SYSTEM_USER
SELECT 'The current system user is: '+ @sys_usr
GO

Here is the result set:

---------------------------------------------------------- 
The current system user is: sa                             

(1 row(s) affected)
B. Use SYSTEM_USER with DEFAULT constraints

This example creates a table using SYSTEM_USER as a DEFAULT constraint for the receptionist for a patient row.

USE pubs
GO
CREATE TABLE appointments2
(
 patient_id int IDENTITY(2000, 1) NOT NULL,
 doctor_id  int NOT NULL,
 appt_date datetime NOT NULL DEFAULT GETDATE(),
 receptionist varchar(30) NOT NULL DEFAULT SYSTEM_USER
)
GO
INSERT appointments2 (doctor_id)
VALUES (151)
INSERT appointments2 (doctor_id, appt_date)
VALUES (293, '5/15/98')
INSERT appointments2 (doctor_id, appt_date)
VALUES (27882, '6/20/98')
INSERT appointments2 (doctor_id)
VALUES (21392)
INSERT appointments2 (doctor_id, appt_date)
VALUES (24283, '11/03/98')
GO

This is the query to select all the information from the appointments2 table:

SELECT * 
FROM appointments2
ORDER BY doctor_id
GO

Here is the result set:

patient_id  doctor_id   appt_date                receptionist    
----------- ----------- ------------------------ --------------- 
2000        151         Mar 4 1998 10:36AM       sa              
2001        293         May 15 1998 12:00AM      sa              
2003        21392       Mar 4 1998 10:36AM       sa              
2004        24283       Nov 3 1998 12:00AM       sa              
2002        27882       Jun 20 1998 12:00AM      sa              

(5 row(s) affected)

See Also

Allowing Null Values

ALTER TABLE

CREATE TABLE

CURRENT_TIMESTAMP

CURRENT_USER

Managing Security

SESSION_USER

System Functions

USER

Using Constraints, Defaults, and Null Values