A developer got tasked with removing the hard coded references to database schema names in the code. Just delete the schema from the SQL was trivial. Testing the changes was a whole different problem. I went to the developer to get help with my Clearcase problems. In return he asked if I could get him through his testing problems. The app kept giving him error messages stating that he did not have the appropriate database role. I had him trace the code that made the app display this error message.
One would think if you ran the same SQL as the app, you could determine the app results by interpreting the SQL results. This is not always so. I helped the developer make the decision to run all the SQL as himself (instead of doing it as the schema owner). As soon as he tried this, he could not even run the equivalent SQL. Turns out his database account does not even see the database tables used in the SQL.
Our task was not complete. I prodded the developer to delve a little deeper. So I had him check whether his database account could even see the stored procedure that executes the SQL. Now that he saw the pattern, I released him to compile a list of database objects he needed new synonyms created for. The DBAs could help create them. But we needed to do the analysis on which synonyms we needed.
This adventure was another example where you need to question all assumptions to get to the root cause of a problem.
