SYSTEM_USER (Transact-SQL)

SYSTEM_USER (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

SYSTEM_USER (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions

Syntax

SYSTEM_USER

Return Types

nchar

Remarks

You can use the SYSTEM_USER function with DEFAULT constraints in the CREATE TABLE and ALTER TABLE statements. You can also use it as any standard function.

If the user name and login name are different, SYSTEM_USER returns the login name.

If the current user is logged in to SQL Server by using Windows Authentication, SYSTEM_USER returns the Windows login identification name in the form: DOMAIN\user_login_name. However, if the current user is logged in to SQL Server by using SQL Server Authentication, SYSTEM_USER returns the SQL Server login identification name, such as WillisJo for a user logged in as WillisJo.

SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER returns the name of the impersonated context.

Examples

A. Using SYSTEM_USER to return the current system user name

The following example declares a char variable, stores the current value of SYSTEM_USER in the variable, and then prints the value stored in 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: WillisJo                             

(1 row(s) affected)

B. Using SYSTEM_USER with DEFAULT constraints

The following example creates a table with SYSTEM_USER as a DEFAULT constraint for the SRep_tracking_user column.

USE AdventureWorks;
GO
CREATE TABLE Sales_Tracking
(
    Territory_id int IDENTITY(2000, 1) NOT NULL,
    Rep_id  int NOT NULL,
    Last_sale datetime NOT NULL DEFAULT GETDATE(),
    SRep_tracking_user varchar(30) NOT NULL DEFAULT SYSTEM_USER
)
GO
INSERT Sales_Tracking (Rep_id)
VALUES (151)
INSERT Sales_Tracking (Rep_id, Last_sale)
VALUES (293, '5/15/98')
INSERT Sales_Tracking (Rep_id, Last_sale)
VALUES (27882, '6/20/98')
INSERT Sales_Tracking (Rep_id)
VALUES (21392)
INSERT Sales_Tracking (Rep_id, Last_sale)
VALUES (24283, '11/03/98')
GO

The following query to selects all the information from the Sales_Tracking table:

SELECT * FROM Sales_Tracking ORDER BY Rep_id;
GO

Here is the result set.

Territory_id Rep_id Last_sale            SRep_tracking_user    
-----------  -----  -------------------- ------------------ 
2000         151    Mar 4 1998 10:36AM   ArvinDak              
2001         293    May 15 1998 12:00AM  ArvinDak              
2003         21392  Mar 4 1998 10:36AM   ArvinDak              
2004         24283  Nov 3 1998 12:00AM   ArvinDak              
2002         27882  Jun 20 1998 12:00AM  ArvinDak              

(5 row(s) affected)

A. Using SYSTEM_USER to return the current system user name

B. Using SYSTEM_USER with DEFAULT constraints

Reference

Other Resources

Help and Information