SQL Server SYSTEM_USER() Function

SQL Server SYSTEM_USER() Function

The SQL Server SYSTEM_USER() function returns the login name of the current user. This function is useful for identifying the SQL Server login used to connect to the database.

Syntax

SELECT SYSTEM_USER;

The SYSTEM_USER() function does not take any arguments.

Example SQL Server SYSTEM_USER() Function Queries

Let's look at some examples of SQL Server SYSTEM_USER() function queries:

1. Basic SYSTEM_USER() Example

SELECT SYSTEM_USER AS login_name;

This query returns the login name of the current user. The result will be:

login_name
--------------
username

2. Using SYSTEM_USER() in a Table

CREATE TABLE user_actions (
    action_id INT PRIMARY KEY,
    action_description VARCHAR(255),
    user_name VARCHAR(50) DEFAULT SYSTEM_USER
);
INSERT INTO user_actions (action_id, action_description) VALUES (1, 'Logged In');
SELECT * FROM user_actions;

This example creates a table named user_actions with a column that defaults to the login name of the current user. The result will be:

action_id  action_description  user_name
---------  -------------------  ---------
1          Logged In            username

3. SYSTEM_USER() with a Variable

DECLARE @login_name VARCHAR(50);
SET @login_name = SYSTEM_USER;
SELECT @login_name AS login_name;

This query uses a variable to store the login name of the current user and then returns it. The result will be:

login_name
--------------
username

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SYSTEM_USER() function.

Step 1: Creating a Table

This step involves creating a new table named logs to store some sample data with user actions.

CREATE TABLE logs (
    log_id INT PRIMARY KEY,
    log_message VARCHAR(255),
    log_user VARCHAR(50) DEFAULT SYSTEM_USER
);

In this example, we create a table named logs with columns for log_id, log_message, and log_user.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the logs table.

INSERT INTO logs (log_id, log_message) VALUES (1, 'User logged in');
INSERT INTO logs (log_id, log_message) VALUES (2, 'User viewed a page');
INSERT INTO logs (log_id, log_message) VALUES (3, 'User logged out');

Here, we insert data into the logs table.

Step 3: Using the SYSTEM_USER() Function

This step involves using the SYSTEM_USER() function to retrieve the login name of the current user for each log entry.

SELECT log_id, log_message, log_user
FROM logs;

This query retrieves the log_id, log_message, and log_user for each row in the logs table. The result will be:

log_id  log_message        log_user
------  -------------      --------
1       User logged in     username
2       User viewed a page username
3       User logged out    username

Conclusion

The SQL Server SYSTEM_USER() function is a useful tool for identifying the login name of the current user in the SQL Server database. Understanding how to use the SYSTEM_USER() function and its syntax is essential for effective user management and data processing in SQL Server.