SYSTEM_USER (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.
Transact-SQL syntax conventions
Syntax
SYSTEM_USER
Return Types
nvarchar(128)
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.
You cannot EXECUTE AS a SYSTEM_USER.
Azure SQL Database: The SYSTEM_USER statement doesn't support execution using an impersonated security context through EXECUTE AS.
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 AdventureWorks2022;
GO
CREATE TABLE Sales.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.Sales_Tracking (Rep_id)
VALUES (151);
INSERT Sales.Sales_Tracking (Rep_id, Last_sale)
VALUES (293, '19980515');
INSERT Sales.Sales_Tracking (Rep_id, Last_sale)
VALUES (27882, '19980620');
INSERT Sales.Sales_Tracking (Rep_id)
VALUES (21392);
INSERT Sales.Sales_Tracking (Rep_id, Last_sale)
VALUES (24283, '19981130');
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)
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
C. Using SYSTEM_USER to return the current system user name
The following example returns the current value of SYSTEM_USER
.
SELECT SYSTEM_USER;
See Also
ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
CURRENT_TIMESTAMP (Transact-SQL)
CURRENT_USER (Transact-SQL)
SESSION_USER (Transact-SQL)
System Functions (Transact-SQL)
USER (Transact-SQL)