0

In Azure SQL I can query what temp tables currently exist by using the query -

select * from tempdb.sys.tables;

However, I am not able to find who created these. Surely there must be a simple way to find out who created these temp tables! There are links which suggest things, but all of that works on SQL Server, not Azure SQL.

TS74
  • 61
  • 3

1 Answers1

0

Permissions

Any user can create temporary objects in tempdb. Users can access only their own objects, unless they receive additional permissions. It's possible to revoke the connect permission to tempdb to prevent a user from using tempdb. We don't recommend it because some routine operations require the use of tempdb.

The tempdb system database is a global resource that's available to all users connected to the instance of SQL Server or connected to Azure SQL Database.

By default, server admin, database owner or a user with required permission can access the tables of tempdb.

This official article on tempdb database is related to Azure SQL Database. Please go thorugh for more details and better understanding.

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14
  • My question is how can we find who created the local and global temporary tables beginning with # and ## which are visible in the query mentioned above, but the owner column does not exist. – TS74 Jan 11 '22 at 09:43
  • there is no owner name of tempdb. the tempdb is all about database metadata and has no relation with the owner/user. `tempdb` is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. – Utkarsh Pal Jan 11 '22 at 10:34
  • UtkarshPal-MT Thanks. But you haven't read my question properly. – TS74 Jan 14 '22 at 16:09