How to find the user of a given temp table?

85 views Asked by At

I am trying to find out the user who created the specific Temporary table in SQL Server 2019.

CREATE TABLE #Test123
(
    id INT,
    name VARCHAR(1)
);

I found the below solution from the old stack over flow post. For example, the temp table 'Test123' was created by the user 'ms123', but the below query showing it as 'sa'

SELECT 
    SUSER_NAME(CONVERT(INTEGER, OBJECTPROPERTYEX(t.OBJECT_ID, 'OwnerId')))
FROM 
    tempdb.sys.tables AS t
WHERE 
    t.name LIKE N'#Test123%';

I tried the below query, but its not showing the user details,

USE tempdb;

SELECT * 
FROM sys.tables 
WHERE name LIKE '%#Test123%';

Thanks in advance

0

There are 0 answers