求助：Azure Data Studio Profiler无法显示数据库名和登录名

Hey there! Let’s work through your problem—empty LoginName and DatabaseName fields in Azure Data Studio (ADS) Profiler can be frustrating, but we’ve got several solid ways to fix that and capture all user-triggered events in your organization.

First: Fix the ADS Profiler Display Issue #

Before jumping to other tools, let’s make sure your Profiler session is set up correctly:

• Verify Data Columns are Selected
  When creating or editing your Profiler session, head to the Data Columns tab. Make sure you’ve checked both LoginName and DatabaseName—these fields aren’t always included by default. Restart the session after making changes to apply the update.

• Check Your Permissions
  The account you’re using to run ADS Profiler needs sufficient permissions to pull these details:

  • For SQL Server: You’ll need the ALTER TRACE server-level permission.
  • For Azure SQL Database/Managed Instance: Grant VIEW DATABASE STATE (database-level) or VIEW SERVER STATE (server-level, for cross-database tracking) to your account. Without these, Profiler can’t access login or database metadata.

• Target User-Focused Events
  Some system events don’t populate LoginName or DatabaseName. Stick to user-triggered events like SQL:BatchCompleted, RPC:Completed, or Login to ensure you’re capturing relevant data that includes these fields.

Second: Use T-SQL Queries for Direct Event Capture #

If ADS Profiler still isn’t cooperating, T-SQL gives you more control over the data you capture. Here are two reliable approaches:

Option 1: Extended Events (Recommended) #

Extended Events are the modern, lightweight replacement for Profiler—they have minimal performance impact and capture more detailed data. Create a session to track user activity with login and database details:

-- Create the extended events session
CREATE EVENT SESSION [UserActivityTracker] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.login_name, sqlserver.database_name, sqlserver.session_id)),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.login_name, sqlserver.database_name, sqlserver.session_id))
ADD TARGET package0.event_file(SET filename=N'UserActivityTracker.xel')
WITH (STARTUP_STATE=OFF);
GO

-- Start the session to begin capturing data
ALTER EVENT SESSION [UserActivityTracker] ON SERVER STATE = START;
GO

-- Query the captured event data
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventType,
    event_data.value('(event/action[@name="login_name"]/value)[1]', 'varchar(100)') AS LoginName,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(100)') AS DatabaseName,
    event_data.value('(event/data[@name="batch_text"]/value)[1]', 'nvarchar(max)') AS QueryText,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime
FROM 
    (SELECT CAST(event_data AS XML) AS event_data
     FROM sys.fn_xe_file_target_read_file('UserActivityTracker*.xel', NULL, NULL, NULL)) AS x;
GO

You can adjust the events (e.g., add Login or Logout events) based on what you need to track.

Option 2: System Dynamic Management Views (DMVs) #

If you just need recent user queries, DMVs can pull cached query data. Note: This only shows queries still in the query cache—older queries may be missing.

SELECT 
    s.login_name,
    DB_NAME(q.dbid) AS DatabaseName,
    q.text AS QueryText,
    s.host_name,
    q.last_execution_time
FROM 
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS q
JOIN 
    sys.dm_exec_sessions AS s ON qs.session_id = s.session_id
WHERE 
    s.is_user_process = 1 -- Filter out system processes
ORDER BY 
    q.last_execution_time DESC;

Third: Alternative Tools to Capture User Events #

If you need more robust tracking, consider these options:

• SSMS Profiler: Sometimes SQL Server Management Studio’s Profiler handles permission and field capture more reliably than ADS. Give it a try with the same event and column settings.
• Azure Monitor (for Azure SQL): If you’re using Azure SQL Database or Managed Instance, configure diagnostic settings to send SQL query logs to Log Analytics. You can then use Kusto queries to filter for user events, with full access to login_name and database_name fields.

Final Notes #

Start with adjusting your ADS Profiler settings and verifying permissions—this is often the quickest fix. If that doesn’t work, extended events are the most powerful and efficient way to capture all user-triggered events long-term.

内容的提问来源于stack exchange，提问作者Ravi Kiran