MMartin1 (6/28/2016)
I am wondering about the three part naming convention and its substitution with a synonym. I envision a scenario where the code reads like
/* block 1*/
SELECT ...
FROM ProductionDB_test.dbo.tableA
I instead use the synonym for tableA
/* block 2*/
SELECT ...
FROM synTableA
If I present /* block 1*/ to a live system (assuming no synonym) with this wrong database I would think the statement would fail as it should. I dont think there should be a database named "ProductionDB_test" on a live production system. Else if there is such a database there then what about getting it wrong in the synonym when you have to maintain plenty of synonyms and an error entry sneaks in? Just wondering what prompts security with synonyms on three parts named objects. Thanks for the good article!
The second block should use the 2 part naming convention. It helps prevent schema accidents and helps a bit (at least it used to back in the day) with performance.
As for getting synonyms wrong, sure, I agree. It's certainly possible but, once established, synonyms usually don't change and you normally don't restore Dev to Prod to do promotions. As with all else, though, "It Depends". For us, such synonyms have been a life saver because of a sometimes silly but usually effective requirement to name databases with an "underscore environment" extension. This also allows us to have multiple nearly identical databases on Dev boxes so that multiple different tacks on development of large batch processing systems can simultaneously exist without necessarily creating a full environment (other utility, staging, and security databases) for each endeavor.
--Jeff Moden
Change is inevitable... Change for the better is not.