SQL Server CURRENT_USER() Function
Last Updated :
06 Sep, 2024
The CURRENT_USER()
function in SQL Server is a useful tool for identifying the currently logged-in user executing a query. This function helps track user activity and enforce security policies and is often used in auditing scenarios. In this article, We will learn about SQL Server CURRENT_USER() Function in detail.
SQL Server CURRENT_USER() Function
- The
CURRENT_USER()
function in SQL Server returns the name of the current user that is executing the query.
- It identifies the SQL Server login under which the current session is running.
- This function is often used to track or log the activities of the user or to enforce security measures based on user identity.
Syntax
CURRENT_USER() function syntax is:
SELECT CURRENT_USER;
The function will return the username associated with the SQL Server login for the current session.
Examples of SQL Server CURRENT_USER() Function
Let's look at a few examples of the CURRENT_USER() function in SQL Server.
Example 1:
Using CURRENT_USER () function and getting the current user’s name.
SELECT CURRENT_USER AS logged_in_user;
Output:
Here, CURRENT_USER()
returns the name AdminUser
, which is the identity of the user executing the query
Example 2:
Using CURRENT_USER as a default value in the below example and getting the output.
Query:
CREATE TABLE user01 (
user_id int IDENTITY(100, 2) NOT NULL,
customer_id int NOT NULL,
user_name char(50) NOT NULL DEFAULT CURRENT_USER
);
INSERT INTO user01 (customer_id)
VALUES (101), (102);
Displaying the contents of the table -
SELECT * FROM user01;
Output :
| user_id | customer_id | user_name |
1 | 100 | 101 | nidhi |
2 | 101 | 102 | nidhi |
Here, firstly we need to create a table then insert values into it then generate the required output using CURRENT_USER function as a default value.
Note: For running above code use SQL server compiler, you can also use an online compiler.\
Example 3:
Using CURRENT_USER () function and impersonating user 'Geek'.
SELECT CURRENT_USER;
EXECUTE AS USER = 'Geek';
SELECT CURRENT_USER;
Output :
nidhi
Geek
Here, we are impersonating user as 'Geek', then calling CURRENT_USER function to get the current user's name.
Example 4:
Using CURRENT_USER () function for impersonating user 'Geek' then again reverting the code to get the previous current user.
SELECT CURRENT_USER;
EXECUTE AS USER = 'Geek';
SELECT CURRENT_USER;
REVERT;
SELECT CURRENT_USER;
Output :
nidhi
Geek
nidhi
Conclusion
The CURRENT_USER()
function is an important feature in SQL Server, enabling users to determine the identity under which queries are executed. It is valuable for auditing, security, and tracking purposes, making it a frequently used function in database management. From getting the current user's name to impersonating users in a controlled environment, CURRENT_USER()
serves many practical purposes in managing user sessions effectively.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you want to create, delete, update or read data, SQL provides the structure and commands to perform these operations. SQL is widely supported across various database systems like MySQL, Oracl
8 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
5 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15+ min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read
Window Functions in SQL SQL window functions are essential for advanced data analysis and database management. It is a type of function that allows us to perform calculations across a specific set of rows related to the current row. These calculations happen within a defined window of data and they are particularly useful
6 min read
SQL | WITH Clause SQL queries can sometimes be complex, especially when you need to deal with multiple nested subqueries, aggregations, and joins. This is where the SQL WITH clause also known as Common Table Expressions (CTEs) comes in to make life easier. The WITH Clause is a powerful tool that simplifies complex SQ
6 min read
SQL Exercises : SQL Practice with Solution for Beginners and Experienced SQL (Structured Query Language) is a powerful and flexible tool for managing and manipulating relational databases. Regardless of our experience level, practising SQL exercises is essential for improving our skills. Regular practice not only enhances our understanding of SQL concepts but also builds
15+ min read
SQL Cheat Sheet ( Basic to Advanced) Creating and managing databases in SQL involves various commands and concepts that handle the structuring, querying, and manipulation of data. In this guide, we will see a comprehensive cheat sheet for essential SQL operations, offering a practical reference for tasks ranging from database creation
15 min read