0

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.

1 Answer 1

1

My first question... Could this be the cause of test data getting into production with this setup?

Sure, if your QA team and other tests happen in the production database on the production server. Aside from what sounds like a problematic design with global temp tables, this is a red flag in itself, if you guys do your testing in the production database.

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.

That's going to heavily depend on where and how these temp tables are used, and we can't provide a complete answer with the information provided.

If they are used within only the scope of the stored procedure that creates them, or in the chain of calling stored procedures, then a local temp table would be in scope. But if these global temp tables are used in other places that would be out of scope for a local temp table, such as with BCP to generate a drop file to a folder share, then it won't be as simple as switching to a local temp table. It'll probably involve re-designing that specific process.

3
  • It's the same SQL Server instance, but two separate databases, lets say one called Prod and the other Prod_COPY_FOR_DEV. But they both have identical copies of the stored procedures with the global temp tables having identical naming. Jan 19, 2023 at 19:51
  • 1
    @BrianKarabinchak Yes, even so, it's possible to access the same global temporary table from procedures in both databases. It is still dangerous and bad practice (this being one example why) to have your PROD and DEV copies of the database on the same server. Ideally they shouldn't be able to communicate with each other, when setup to best practice.
    – J.D.
    Jan 19, 2023 at 22:40
  • Right so that is one issue. The other one I think that could crop is then if one person at a one plant pressed the button the calls the SP that saves the records, and someone at another plant at the same time did pressed it, then these two could also run into each even just within production the database (lets assume the dev has been moved). Jan 20, 2023 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.