Is it possible to read data in another session's temporary table?

Unwieldy but you can examine the tables pages from an admin logon.

Get object id;

select object_id from tempdb.sys.tables where name like '#mystuff%'

Get a list of allocated pages;

dbcc ind('tempdb', <object id>, -1)

for each of the PageFID / PagePID (file/page IDs)

dbcc traceon(3604);
dbcc page(tempdb, <PageFID>, <PagePID>, 3) with tableresults

If I create #mystuff from another session I can see in a dbcc page view from my own session:

Slot 0 Offset 0x60 Length 18    Slot 0 Column 1 Offset 0xb Length 7 Length (physical) 7 myFieldName MyValue

If you prefix a temporary table name with two octothorpes, e.g. ##mystuff, it creates a global temporary table that exists outside session scope.

That does require you to be able to alter the query text, which may or may not be accessible in this specific case.

Related