I decided to accept Grant’s (TheScaryDBA) challenge found here- http://www.scarydba.com/2017/03/02/random-blog-post-challenge/ where we have to write a technical blog post that incorporates a certain image of a certain individual.

There was someone quite mischievous on my SQL Server, I needed to find out what he was doing. So I decided to track a specific login against a specific database using extended events.

The Code:

CREATE EVENT SESSION TrackDrWho ON SERVER 

ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
	sqlserver.client_hostname, sqlserver.database_id,sqlserver.is_system,sqlserver.username,sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'MrBaker')
	AND [sqlserver].[database_id]=(6) AND ( [sqlserver].[is_system] = ( 0 ))))

ADD TARGET package0.event_file(SET filename=N'c:\temp\TrackDrWho')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF)
GO

ALTER EVENT SESSION TrackDrWho
ON SERVER
STATE = start;

 

By default the information returned is not in a friendly format.

SELECT data FROM
 (SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
 ('c:\temp\TrackDrWho*.xel',
  'c:\temp\TrackDrWho*.xem', NULL, NULL)
 ) Trackers;
 GO
 

I decided to use XQuery to shred the XML but even that wasn’t perfect because I wanted to see only the statements generated by the user and not really the system generated code behind the user transactions so I threw the contents into a table for further analysis.

SELECT
data.value (
			'(/event[@name="sql_batch_completed"]/@timestamp)[1]', 'DATETIME') AS [TimeLogged],

data.value (
			'(/event/action[@name="database_id"]/value)[1]', 'INT') AS [DatabaseID],
data.value (
			'(/event/action[@name="username"]/value)[1]', 'VARCHAR(MAX)') AS [Person],
data.value (
			'(/event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS [SQL Code] INTO dbo.GetHim
FROM
 (SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
 ('c:\temp\TrackDrWho*.xel',
  'c:\temp\TrackDrWho*.xem', NULL, NULL)
 ) Trackers

 SELECT * FROM dbo.GetHim
 WHERE [SQL Code] NOT LIKE '%SERVERPROPERTY%'

 

I wasn’t impressed with the activity and a conversation followed:

BlobEater: ” I see you Mr Baker….. and I am not happy”.

Mr Baker Replies: (See Image)

BlobEater: “I am watching you!”

P.S. Here is the cleanup-code if needed.

 DROP TABLE dbo.GetHim

 ALTER EVENT SESSION TrackDrWho
ON SERVER
STATE = STOP;

DROP EVENT SESSION TrackDrWho ON SERVER
GO