Monitoring data exflitration

Question

I've been tasked with coming up with some mechanism that would allow me to detect when a database user queries a certain table in our system and a large result set is returned (say over 1000 rows). This is done under the premise that these large queries might be part of a data exfiltration attempt and should be reviewed by data security.

I want to log all queries where:

data from table X is included in the result set. The results contains more than 1000 rows.

How would I go about capturing this information?

Can this be accomplished using extended events? Query Store?

Answer 1

Perhaps you can implement this with an Extended Event like this. You probably want to finetune this, for example removing SSMS or other internal stuff.

It will fetch the rpc_completed and batch_completed where the row_count > 1000. I assumed it would make sense to also retrieve the client_hostname and client_app_name when it happens.

CREATE EVENT SESSION [MoreThan1000RowsReturned] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname)
    WHERE ([row_count]>(1000))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname)
    WHERE ([row_count]>(1000)))
ADD TARGET package0.event_file(SET filename=N'MoreThan1000RowsReturned',max_file_size=(100))
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