SQL SYSTEM_USER

The SYSTEM_USER function in SQL returns the name of the current system user connected to the database. This function is useful for tracking which user is executing a query, ensuring database security, and maintaining audit logs.

In this tutorial, we will explore the SYSTEM_USER function, its syntax, and practical examples to understand its usage.

Syntax of SQL SYSTEM_USER

The SYSTEM_USER function does not require any parameters and can be used as follows:

</>
Copy
SELECT SYSTEM_USER;

Explanation:

  • SYSTEM_USER retrieves the name of the current database user executing the query.
  • It returns a system-defined username that represents the login identity.

Step-by-Step Examples Using SQL SYSTEM_USER

1. Checking the Current System User

Let’s find the login identity of the user connected to the database.

</>
Copy
SELECT SYSTEM_USER AS current_user_1;

Output (example):

Explanation:

  • The query returns the name of the currently logged-in user (in this case, DESKTOP-MDIM8CL\mmm04).
  • It helps database administrators track which user executed a query.

2. Logging User Actions with SYSTEM_USER

In a real-world scenario, we may want to log the database user performing specific actions. Let’s create a user_activity table to store login events.

In SQL Server:

</>
Copy
CREATE TABLE user_activity (
    id INT PRIMARY KEY IDENTITY(1,1),
    username VARCHAR(50),
    action VARCHAR(100),
    activity_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

Now, let’s insert a record showing that system user performed a data update:

</>
Copy
INSERT INTO user_activity (username, action)
VALUES (SYSTEM_USER, 'Updated product details');

Explanation:

  • The SYSTEM_USER function captures the current user executing the query.
  • The action performed is logged along with a timestamp.
  • This approach is useful for auditing database changes.

To view the log, we can retrieve records from the user_activity table:

</>
Copy
SELECT * FROM user_activity;

Output (example):

Conclusion

The SYSTEM_USER function in SQL is used to retrieve the current database user and logging actions for auditing purposes. In this tutorial, we explored:

  1. The syntax and usage of the SYSTEM_USER function.
  2. A simple example to check the current user.
  3. A practical use case for tracking user actions in a database.