Practical Uses for Synonyms in SQL Server

Question

Post reply

Practical Uses for Synonyms in SQL Server

Viewing 6 posts - 76 through 80 (of 80 total)

You must be logged in to reply to this topic. Login to reply

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 1

Not that you'd necessarily want to, but you can't reference a synonym on a linked server by its four part name.

--
Scott

aj2red SSC Veteran Points: 202 More actions · Answer 2

We use pass through views for reporting purposes.

synonyms for stored procedures.

Alan G-436699 Mr or Mrs. 500 Points: 504 More actions · Answer 3

I've recently discovered a great use of synonyms. My company won't refresh UAT from production and UAT is years out of date. This has forced me to develop in production (all my queries would return zero rows in UAT). I've used synonyms to write to "temporary test" tables in production. Then, when the test tables are signed off, i flip the synonyms to the production tables and the procs are all good to go.

bkubicek SSChampion Points: 10948 More actions · Answer 4

Hi Jason,

I am thinking about doing an editorial on synonyms. Wondering if I could have permission to reverence your article?

Thanks,
Ben

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

bkubicek - Wednesday, December 5, 2018 12:20 PM
Hi Jason,
I am thinking about doing an editorial on synonyms. Wondering if I could have permission to reverence your article?
Thanks,
Ben

No problem on my end.

The Redneck DBA

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Alan G-436699 - Friday, July 1, 2016 1:44 PM
I've recently discovered a great use of synonyms. My company won't refresh UAT from production and UAT is years out of date. This has forced me to develop in production (all my queries would return zero rows in UAT). I've used synonyms to write to "temporary test" tables in production. Then, when the test tables are signed off, i flip the synonyms to the production tables and the procs are all good to go.

Just a word of caution... first, it sounds like your UAT databases are on the same instance as your prod databases. Most auditors will fail your company during an audit for such a thing.

The other thing is, even though you've take a good precaution in making copies of tables instead of using actual production tables, you're still messing around in production and your test tables are taking extra time and space for production backups and the inevitable restores. Unfortunately, copying the tables to the UAT environment would be a violation of the rules that YOU would be held accountable for if there were a breach.

The bottom line is that you are placing yourself in harms way and the company can blame YOU for any problems with production or if there's a breach no matter how careful you are because it sounds like you didn't actually get permission to "develop in production". The company needs to get their act together and make it possible for Developers to do their job correctly. One of those things would be to (sorry but it's true) remove your permissions to do anything in production because Developers simply should not have the privs in production to do the things you've done.

I DO salute you for trying to do your job to help the company but you are placing yourself at great risk if anything goes wrong even if nothing goes wrong because of you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)