0

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]

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wilq
  • 1
  • 1
  • How are you trying to deploy this script? From Python? Using pyodbc by chance? – AlwaysLearning Dec 22 '21 at 22:25
  • I was trying using it in python with SQL alchemy engine.execute, But later I tried also MSSMS and it didn't work too. – Wilq Dec 23 '21 at 09:01
  • I would expect it to work in SSMS and not in pyodbc. pyodbc has the autocommit=false/true setting that controls whether it automatically commits after each operation or whether you must call connection.commit manually, but the upshot is that everything it does is inside a transaction so wouldn't work for this type of operation. It should work inside SSMS, however. – AlwaysLearning Dec 23 '21 at 11:38
  • I asked my colleague with admin on this server to run same script. It ran with no problem. After running SELECT * FROM fn_my_permissions(NULL, 'SERVER') where permission_name like '%EVENT%' It seems that we had same 'event' permissions on server. – Wilq Dec 23 '21 at 14:23

0 Answers0