How to get the email address of the current user from an Azure SQL Database

When working with an Azure SQL Database, you may find the need to determine who is currently connected. This could be for auditing purposes, for populating a ‘Created By’ column, or simply for troubleshooting a permissions issue.

If your users authenticate using Microsoft Entra ID (formerly Azure Active Directory), the identity in question is usually their email address. However, it isn’t immediately obvious where you can get hold of this information via a query.

In this article, I will show you a simple SQL query that returns the email address of the current user, and how you can easily run this from C# code using a SqlConnection (or any other DbConnection).

The SQL query

Here is the SQL query you can run against an Azure SQL database, or a regular SQL Server database for that matter, to retrieve the login name of the current user.

SELECT TOP 1 login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND session_id = @@SPID;

The query selects from the sys.dm_exec_sessions dynamic management view, which returns one row per authenticated session. The login_name column contains the name of the login that the session is running under. When you are connected using Microsoft Entra authentication, this will be the user’s sign-in name, which is typically their email address e.g. user.name@example.com.

The WHERE clause is where the important filtering happens. Filtering where is_user_process equals 1 excludes internal system sessions, and comparing session_id to @@SPID (which returns the session ID of the current connection) ensures that we only get the session that is running the query.

Since @@SPID can only match a single session, the TOP 1 isn’t strictly required. However, I like to include it as a defensive measure so that the query is guaranteed to return one row at most.

You don’t need any special permissions to run this query, as a user is always able to see their own session information.

Note that if the session has switched context using EXECUTE AS, login_name will reflect the impersonated login. If you need the name of the login that originally connected, use the original_login_name column instead.

Using the query from C#

To use the query in a .NET application, we can implement a repository method or extension method that operates on the DbConnection type. Since SqlConnection derives from DbConnection, the method will be available on a SqlConnection instance, as well as anywhere else you are working with the base type.

An example of the extension method approach is shown below.

using System.Data;
using System.Data.Common;
 
public static class DbConnectionExtensions
{
    private const string CurrentUserSql = """
        SELECT TOP 1 login_name
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1 AND session_id = @@SPID;
        """;
 
    public static async Task<string?> GetCurrentUserAsync(
        this DbConnection connection,
        CancellationToken cancellationToken = default)
    {
        if (connection.State != ConnectionState.Open)
        {
            await connection.OpenAsync(cancellationToken);
        }
 
        using var command = connection.CreateCommand();
        command.CommandText = CurrentUserSql;
 
        var result = await command.ExecuteScalarAsync(cancellationToken);
 
        return result as string;
    }
}

The GetCurrentUserAsync method opens the connection if it isn’t already open, creates a command containing the SQL, and calls ExecuteScalarAsync to retrieve the value of the first column of the first row. The result is then returned as a string.

Here is an example of calling the method using a SqlConnection from the Microsoft.Data.SqlClient package.

using Microsoft.Data.SqlClient;
 
var connectionString = "Server=tcp:your-server.database.windows.net,1433;Database=your-database;Authentication=Active Directory Interactive;";
 
await using var connection = new SqlConnection(connectionString);
 
var email = await connection.GetCurrentUserAsync();
 
Console.WriteLine("Connected as: {0}", email);

Note that in a real-world application, the connection string should come from a configuration source.

The Authentication=Active Directory Interactive setting will prompt you to sign in with your Microsoft Entra account. Once you have done so, you should see output similar to the following.

Connected as: user.name@example.com

Things to be aware of

The value returned by login_name depends on how the connection was authenticated. With Microsoft Entra authentication, you’ll get the user’s sign-in name, but with SQL authentication you’ll get the SQL login name, and a managed identity won’t have an email address at all. You shouldn’t assume the value is an email address unless you know how your users connect.

It’s also worth remembering that this query returns the identity of the database connection, which is not always the same as the end user of your application. In a typical web app or API where all database calls are made using a single identity, the query will return that identity for every request. If you need the email address of the person using your application in that scenario, you’ll want to read it from their claims instead.

I recommend that you try selecting everything (*) from the sys.dm_exec_sessions view on your database instance to see if there is any other session information that may be useful for your application (e.g. host_name, program_name, etc.).

Summary

In this article, I explained how to retrieve the email address of the current user from an Azure SQL Database using the sys.dm_exec_sessions view, and how the is_user_process and @@SPID conditions ensure that only the current session is returned.

I then showed how the query can be run from C# by wrapping it in an extension method on DbConnection, and demonstrated using it with a SqlConnection that authenticates via Microsoft Entra ID.

Lastly, I covered a couple of things to be aware of, namely that the returned value depends on the authentication method, and that it represents the identity of the connection rather than necessarily the end user of your application.