I'd like to know who (Service account, user account ,etc ) ran each query_id that Query_Store records. Is there a way to do this? I've looked all over and can't seem to find anything.
Basically I'd like the data from this to be stored as well. Hostname, and Login Name are very useful to me.
SELECT sdest.DatabaseName
,sdes.session_id
,sdes.[host_name]
,sdes.[program_name]
,sdes.client_interface_name
,sdes.login_name
,sdes.login_time
,sdes.nt_domain
,sdes.nt_user_name
,sdec.client_net_address
,sdec.local_net_address
,sdest.ObjName
,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
SELECT db_name(dbid) AS DatabaseName
,object_id(objectid) AS ObjName
,ISNULL((
SELECT TEXT AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
FOR XML PATH('')
,TYPE
), '') AS Query
FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
) sdest
where sdes.session_id <> @@SPID
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id
Credit: Execution datetime for SQL queries against SQL Server
There's no DMV that records which sessions ran which queries. To gather that information you must use an Extended Events trace, or a Database Audit.