Implement a secure environment →mediumMultiple Choice

DP-300 sys.dm_exec_sessions Practice Question

Exhibit

Refer to the exhibit. You have an Azure SQL Database and you run the following KQL query in Azure Monitor:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "SQLSecurityAuditEvents"
| where action_id_s == "LGIF"
| project TimeGenerated, client_ip_s, application_name_s, succeeded_s

You execute the following query:

SELECT c.client_ip, c.application_name FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON s.session_id = c.session_id WHERE s.action_id = 'LGIF' AND s.state = 'ABORT';

What does this query return?

⚠ Common exam trap

Candidates may try to map 'LGIF' and 'ABORT' to login events, but these are not valid filters for these DMVs. The query fails before returning any login data, so the correct outcome is an error/no result, not a list of successful or failed logins.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Successful login events with client IP and application name.

The query as written is invalid. sys.dm_exec_sessions does not contain action_id or state columns; it has a status column with values such as sleeping, running, dormant, and preconnect. Therefore the query would raise an error or return no rows, not successful login events. None of the listed options accurately describes the result. sys.dm_exec_sessions and sys.dm_exec_connections only contain authenticated sessions, so they cannot return failed login data either.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
All security audit events for the database.
Why it's wrong here
Incorrect. Security audit events are not stored in these DMVs.
✗
Failed login events at the server level.
Why it's wrong here
Incorrect. Server-level failed login events are not captured here.
✗
Failed login attempts to the database, including client IP and application name.
Why it's wrong here
Incorrect. Failed login attempts do not create sessions, so they are not represented in these DMVs.
✓
Successful login events with client IP and application name.
Why this is correct
Correct. The DMVs only contain data for successful logins, so the query (if it were valid) would return information about active sessions, which are successful logins.

Go deeper

DP-300 sys.dm_exec_sessions Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question