Capturing queries run by user on SQL Server using extended events

Using Extended Events on SQL 2016 - how can we capture all queries run by users against a specific database. This helps to us understand what developers/reporting analyst/etl developers are running queries on production instance.

Expected Result:
UserName, TSQL Query Ran, Completion Time

You'll want to use the sqlserver.sql_statement_completed event, which includes the statement action field by default. Also, add the package0.collect_system_time action and there are several options for capturing the username.

This example uses sqlserver.session_nt_username to get current session NT user. It will output the results into a file and you will likely need to adjust some of the options as necessary for your environment.

CREATE EVENT SESSION [XE_QUERY_CAPTURE] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed (
    SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.session_nt_username)
    )
ADD TARGET package0.event_file(SET filename=N'C:\Test\XE_QUERY_CAPTURE.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF
    );
GO

ALTER EVENT SESSION [XE_QUERY_CAPTURE] ON SERVER 
STATE = START;

StackExchange Database Administrators Q#216771, answer score: 5