Cannot create event session - SQL Server extended events

640 Views Asked by At

I'm trying to automate extended events logger for a few SQL Servers. I have a template SQL script which should create event session.

CREATE EVENT SESSION [sql-queries-test] ON SERVER
    ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
        ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') AND [sqlserver].[database_name]<>N'master'))
    ADD TARGET package0.event_file(SET filename=N'D:\test\sql-queries-test.xel',max_rollover_files=(0))
    WITH (MAX_MEMORY=102400 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=ON)

Unfortunately running this results in an error

CREATE EVENT SESSION statement cannot be used inside a user transaction.

Accordingly to fn_builtin_permissions I have ALTER ANY EVENT SESSION permission, which is need :

On SQL Server, requires the ALTER ANY EVENT SESSION permission. On SQL Database, requires the ALTER ANY DATABASE EVENT SESSION permission in the database. [source]

0

There are 0 best solutions below