SYSTEM_USER (T-SQL)

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 Microsoft Windows NT® Authentication, SYSTEM_USER returns the Windows NT 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