Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize
This topic has not yet been rated - Rate this topic

SYSTEM_USER

SQL Server 2000

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.