I am working on re-writing some stored procedures. The last issue was an abuse of
EXECUTE AS USER='someUser' to use the default schema and work against those.
The second thing I just noticed though is a large use of global temporary tables. As far as I know global temporary tables are shared across all users and across all sessions. Our production and development database are on the same SQL Server Instance which seems like a problem with this design. The reason being that much of the logic in these stored procedures is dynamically run sql like
SELECT * INTO ##tempTable1 FROM someTable because
someTable is also dynamically generated and the original author didn't want to have to examine
someTable to determine the shape, and the temp table would just take the data no matter the columns, at least that is my suspicion.
I cannot say what this application actually is used for but it is used in many (90+) locations around the US, and these stored procedures are used to get data to the user, or write data into the database. So it seems to me like this has a lot of potential to get the wires crossed - it has happened before where test data got into production and no one knew how, but now that I am working on this I suspect this must have been how - QA was running a test at the same time a real live person was using the production, wires got crossed in a race condition, the test data won out and that is what got saved instead of the real data was lost.
My first question is if my assessment is correct- I am not a SQL Server expert, never used temporary tables before but based on what I read this usage is incredibly problematic, especially for this use case of these stored procedures. Could this be the cause of test data getting into production with this setup?
My second question is what is the solution? Would it be as simple as changing every
## to a
# to make it local to that procedure? Are there any pitfalls I need to be aware of with doing this? Or is there something else I should be doing to make this safer.