session-user-transact-sql.md

title	SESSION_USER (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/06/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	SESSION_USER_TSQL SESSION_USER
dev_langs	TSQL
helpviewer_keywords	usernames [SQL Server] current user names sessions [SQL Server], user names displaying user names viewing user names SESSION_USER function
ms.assetid	3dbe8532-31b6-4862-8b2a-e58b00b964de
caps.latest.revision	26
author	BYHAM
ms.author	rickbyh
manager	jhubbard

SESSION_USER (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

SESSION_USER returns the user name of the current context in the current database.

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
SESSION_USER  

Return Types

nvarchar(128)

Remarks

Use SESSION_USER with DEFAULT constraints in either the CREATE TABLE or ALTER TABLE statements, or use it as any standard function. SESSION_USER can be inserted into a table when no default value is specified. This function takes no arguments. SESSION_USER can be used in queries.

If SESSION_USER is called after a context switch, SESSION_USER will return the user name of the impersonated context.

Examples

A. Using SESSION_USER to return the user name of the current session

The following example declares a variable as nchar, assigns the current value of SESSION_USER to that variable, and then prints the variable with a text description.

DECLARE @session_usr nchar(30);  
SET @session_usr = SESSION_USER;  
SELECT 'This session''s current user is: '+ @session_usr;  
GO  

This is the result set when the session user is Surya:

--------------------------------------------------------------

This session's current user is: Surya

(1 row(s) affected)

B. Using SESSION_USER with DEFAULT constraints

The following example creates a table that uses SESSION_USER as a DEFAULT constraint for the name of the person who records receipt of a shipment.

USE AdventureWorks2012;  
GO  
CREATE TABLE deliveries3  
(  
 order_id int IDENTITY(5000, 1) NOT NULL,  
 cust_id  int NOT NULL,  
 order_date smalldatetime NOT NULL DEFAULT GETDATE(),  
 delivery_date smalldatetime NOT NULL DEFAULT   
    DATEADD(dd, 10, GETDATE()),  
 received_shipment nchar(30) NOT NULL DEFAULT SESSION_USER  
);  
GO  

Records added to the table will be stamped with the user name of the current user. In this example, Wanida, Sylvester, and Alejandro verify receipt of shipments. This can be emulated by switching user context by using EXECUTE AS.

EXECUTE AS USER = 'Wanida'  
INSERT deliveries3 (cust_id)  
VALUES (7510);  
INSERT deliveries3 (cust_id)  
VALUES (7231);  
REVERT;  
EXECUTE AS USER = 'Sylvester'  
INSERT deliveries3 (cust_id)  
VALUES (7028);  
REVERT;  
EXECUTE AS USER = 'Alejandro'  
INSERT deliveries3 (cust_id)  
VALUES (7392);  
INSERT deliveries3 (cust_id)  
VALUES (7452);  
REVERT;  
GO  

The following query selects all information from the deliveries3 table.

SELECT order_id AS 'Order #', cust_id AS 'Customer #',   
   delivery_date AS 'When Delivered', received_shipment   
   AS 'Received By'  
FROM deliveries3  
ORDER BY order_id;  
GO  

[!INCLUDEssResult]

Order # Customer # When Delivered Received By

-------- ---------- ------------------- -----------

5000 7510 2005-03-16 12:02:14 Wanida

5001 7231 2005-03-16 12:02:14 Wanida

5002 7028 2005-03-16 12:02:14 Sylvester

5003 7392 2005-03-16 12:02:14 Alejandro

5004 7452 2005-03-16 12:02:14 Alejandro

(5 row(s) affected)

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

C: Using SESSION_USER to return the user name of the current session

The following example returns the session user for the current session.

SELECT SESSION_USER;  

See Also

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
CURRENT_TIMESTAMP (Transact-SQL)
CURRENT_USER (Transact-SQL)
SYSTEM_USER (Transact-SQL)
System Functions (Transact-SQL)
USER (Transact-SQL)
USER_NAME (Transact-SQL)