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)