SQL Server检索SQL和用户信息的需求

CREATE EVENT SESSION [CaptureSQLExecutions] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_hostname, sqlserver.username)
    WHERE ([sqlserver].[database_name]=N'XXX')
)
ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\CaptureSQLExecutions.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
<p>ALTER EVENT SESSION [CaptureSQLExecutions] ON SERVER STATE = START
GO

SELECT 
CAST(event_data AS XML).value('(/event/action[@name="username"]/text())[1]', 'nvarchar(128)') AS [Username],
CAST(event_data AS XML).value('(/event/data[@name="batch_text"]/value)[1]', 'nvarchar(max)') AS [SQLText]
FROM sys.fn_xe_file_target_read_file('C:\SQLServer\CaptureSQLExecutions*.xel', NULL, NULL, NULL)

USE [master]
GO</p><p>CREATE SERVER AUDIT [SQLExecutionsAudit]
TO FILE 
(   FILEPATH = N'C:\SQLServer\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO</p><p>ALTER SERVER AUDIT [SQLExecutionsAudit] WITH (STATE = ON)
GO</p><p>USE [XXX]
GO</p><p>CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec]
FOR SERVER AUDIT [SQLExecutionsAudit]
ADD (SELECT ON DATABASE::XXX BY [public])
WITH (STATE = ON)
GO

SELECT <em> FROM fn_get_audit<em>file('C:\SQLServer\Audit\SQLExecutionsAudit</em></em>.sqlaudit', NULL, NULL)