Current user function in SQL

It is used to return the name of the current user in sql database server. it does not accept any parameter.

Syntax

CURRENT_USER

Current_user is the function used to get the name of current user

Example 1

In this example, we are going to use the current_user function to get the current user name.

Input

Employee

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

This database is currently used by amrendra.

Code

SELECT CURRENT_USER;#selected current user name

Output

Amrendra

Example 2

In this example,we are using current_user as a default value to get the output.

Algorithm

• Step 1 − Create table

• Step 2 − Make a parameter as default current user

• Step 3 − Insert values into the table

• Step 4 − Call the select function to get the current user name along with other details.

Input

Employee

ID	Salary	Name
1	30000	Amrendra
2	44000	Amrendra
3	50000	Amrendra

Code

CREATE TABLE employee#table named employee is created
(
   id int NOT NULL,salary int NOT NULL,name char(50) NOT NULL DEFAULT CURRENT_USER ()#parameter is given inside table employee
);
INSERT INTO employee(id,salary)
 values (1,30000),(2,31000),(3,32000);#values inserted into table executed by Amrendra
SELECT * FROM employee;#select statement is used to provide the output

Output

ID	Salary	Name
1	30000	Amrendra
2	44000	Amrendra
3	50000	Amrendra

Example 3

In this example,we are using current_user function and impersonating different users.

Algorithm

• Step 1 − Select current user

• Step 2 − Impersonate to different user

• Step 3 − Select current user to get the impersonated user name.

Input

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Here, the code is first executed by Amrendra

Code

SELECT CURRENT_USER;#selected existing user
EXECUTE AS USER='Tutorial';#changed user name
SELECT CURRENT_USER;#changed one to get selected as a current user. 

Output

Amrendra
Tutorial

Example 4

In this example,we are going to impersonate and then revert to get the previous current user.

Algorithm

• Step 1 − Select current user

• Step 2 − Impersonate to different user

• Step 3 − Select current user to get the impersonated user name.

• Step 4 − Use revert to get previous current user

• Step 5 − Select current user to get the previous current user.

Code

SELECT CURRENT_USER;#selected existing user
EXECUTE AS USER='Tutorial';changed user name
SELECT CURRENT_USER;#changed current user get selected
REVERT#reverting to get the previous current user
SELECT CURRENT_USER;previous user gets selected. 

Output

Amrendra
Tutorial
Amrendra

Conclusion

Here, I have used four examples to explain different types to use a current function. In the first one, we have simply written a query to get the current user. In the second one, we have used a default value to get the current user. In third one, we have impersonated different current user. In the fourth one, we have impersonated the current user but then reverted back to get the previous current user and make that the current user for real time.