SQL Server Temporary Table Security

Question

I am part of a project to convert an old AS400 system to SQL server. The server is used for our payroll processing. Part of the payroll process is to break out the percentage of the biweekly wages to each job we work on. To do so in the AS400 process a csv file was loaded from the P&B admin's to the server in a data access area that only the admin had access to. I need to replicate this in the SQL server environment. I am planning on using SQLCMD to execute an import of the local file into a temporary table. I know it is highly unlikely that anyone would query the data in the temporary table at the time its in use but is there anyway to grant only access to the user that executed the SQLCMD script?

Answer 1

A #temporary table is only visible from the session that created it. So even the same user in another session would not be able to see it. If you load a temporary table in SQLCMD it would be visible to that instance of SQLCMD. So a subsequent command could load the data into a permanent table, or open a cursor over it and call a stored procedure for each row, etc.

Alternatively you could provision a private schema for the user and load the data into a permanent table there. Then no other user (other than a database owner or other privileged user) would be able to read it. The user could then move the data into another schema later.

Answer 2

Is there anyway to grant [#temp table] only access to the user that executed the SQLCMD script?

Unfortunately, if your threat model includes database administrators, the answer is NO.

Your only answer valid choice with that threat model is to encrypt the data client side. With SQLCMD, your best and simplest option is AlwaysEncrypted using Microsoft® Command Line Utilities 13.1 for SQL Server or later only.

See Paul White's article on viewing another session's temporary table, which boils down to

Find the #temp table

SELECT  *
FROM    tempdb.sys.tables T
WHERE   T.name LIKE N'#Private[_]%';

Find the pages

SELECT  T.name,
        T.[object_id],
        AU.type_desc,
        AU.first_page,
        AU.data_pages,
        P.[rows]
FROM    tempdb.sys.tables T
JOIN    tempdb.sys.partitions P
        ON  P.[object_id] = T.[object_id]
JOIN    tempdb.sys.system_internals_allocation_units AU
        ON  (AU.type_desc = N'IN_ROW_DATA' AND AU.container_id = P.partition_id)
        OR  (AU.type_desc = N'ROW_OVERFLOW_DATA' AND AU.container_id = P.partition_id)
        OR  (AU.type_desc = N'LOB_DATA' AND AU.container_id = P.hobt_id)
WHERE   T.name LIKE N'#Private%';

Read the supposedly private data

DBCC TRACEON (3604);
DBCC PAGE (tempdb, 1, 173, 3) WITH TABLERESULTS;

Then use the m_nextPage field to read the next page (it's in file_id : page_id) decimal format.