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?
SQL Server – Temporary Table Security
Securitysqlcmdtemporary-tables
Best Answer
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.