I have employed Extended Events to track the DML and DDL operations that are being performed on the tables within a particular database. Apart from this I'm also tracking the procedures that a user(for now I'm the sole user) executes within the database which is being tracked. I'm using the event 'sql_statement_completed' for the same. It captures all the sql statements that are successfully completed, post which I filter the statements to store only Insert, Update, Delete and Drop statements (exec statements are also being stored). Below is the code pertaining to it:
CREATE EVENT SESSION <track> ON SERVER ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.client_hostname, sqlserver.sql_text, sqlserver.username) WHERE ([sqlserver].[database_name]= <database_name> AND [sqlserver].[is_system]=(0) AND (([statement] like 'INSERT%' OR [statement] like 'UPDATE%' OR [statement] like 'DELETE%' OR [statement] like 'DROP%' OR [statement] like 'ALTER%' OR [statement] like 'EXEC%')))) ADD TARGET package0.event_file( SET filename=N'C:\ExtendedEvents\track.xel', max_file_size=(102400), max_rollover_files=(10000), metadatafile=N'C:\ExtendedEvents\track.xem') WITH (MAX_MEMORY=1048576 KB, EVENT_RETENTION_MODE=NO_EVENT_LOSS, MAX_DISPATCH_LATENCY=5 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)
Now while this has been tracking the DML and DDL statements that are being executed against the tables in the database, it has also been tracking some statements that have not been executed by the user directly(particularly some procedural statements that seem to have been executed by the system itself).
The pic below subsumes the statements(not directly fired the user) that have been tracked:
This is despite the fact that I have explicitly set
[sqlserver].[is_system]=(0) in the
where predicate of
to disable tracking of system fired statements and only track the user fired statements.
So why is it tracking system fired statements as shown in the pic above? How to circumvent the same?