SESSION_USER() Function in SQL Server

SESSION_USER() :

This function in SQL Server is used to return the current user’s name in the database of SQL Server that is in use.

Features :

• This function is used to find the current user’s name.
• This function comes under Advanced Functions.
• This function doesn’t accept any parameter.

Syntax :

SESSION_USER;

Parameter :

This method doesn’t accept any parameter.

Returns :

It returns the current user’s name in the database of SQL Server which is in use.

Example-1 :

Using SESSION_USER() function and getting the current user’s name.

SELECT SESSION_USER;

Output :

nidhi

Example-2 :

Using SESSION_USER as a default value in the below example and getting the output.

CREATE TABLE user01
(  
user_id int IDENTITY(100, 2) NOT NULL,
customer_id int NOT NULL,
user_name char(50) NOT NULL DEFAULT SESSION_USER
);  
INSERT user01(customer_id)  
VALUES (101);

INSERT user01(customer_id)  
VALUES (102);

SELECT * FROM user01;  

Output :

  | user_id  | customer_id | user_name
-----------------------------------------
1 | 100      | 101         | nidhi
-----------------------------------------
2 | 102      | 102         | nidhi

Here, firstly you need to create a table then insert values into it then generate the required output using the SESSION_USER function as a default value.

Note: For running the above code use SQL server compiler, you can also use an online compiler.

Example-3 :

Using SESSION_USER () function and impersonating user ‘Geek’.

SELECT SESSION_USER;  
EXECUTE AS USER = 'Geek';  
SELECT SESSION_USER;  

Output :

nidhi
Geek

Here, we are impersonating the user as ‘Geek’, then calling the SESSION_USER function to get the current user’s name.

Example-4 :

Using SESSION_USER() function for impersonating user ‘Geek’ then again reverting the code to get the previous current user.

SELECT SESSION_USER;  
EXECUTE AS USER = 'Geek';  
SELECT SESSION_USER;  
REVERT;
SELECT SESSION_USER;

Output :

nidhi
Geek
nidhi

Application :

This function is used to find the current user’s name in the database of the SQL server.