Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Accessing Synonyms Via Linked Server
Published: Jun 25, 2017
This was new to me and something which has appeared as a major downside for me when considering whether to use synonyms or views within my databases.

Basically, if you are using synonyms within your database and you then attempt to access those synonyms via a linked server then you will receive an error. SQL Server cannot access synonyms via a linked server.

This seemed like utter madness to me, but it’s true. And it also doesn’t provide a helpful error message to troubleshoot either.

Let’s do a quick demo to test this:

In order to make this work fully I needed to use my home lab (VM Ware running a pair of 2 node SQL 2014 clusters).

I’m not going to go through too much of this step by step as it would take too long, therefore I’ll just explain the key parts:

I’ve created and populated a small table on my TRACYSQL server. I’ve also got a linked server set up to my ROBINSQL cluster:

set nocount on

create database testDB

use testDB

create table testRealTable
id int identity

insert into testRealTable default values
go 100

On my ROBIN SQL cluster I have a linked server through to TRACYSQL and I create a test database and then a synonym pointing to our table on TRACYSQL:

set nocount on

create database testDB

use testDB

create synonym testRealTable for TracySQL.testDB.dbo.testRealTable

Okay, now all we need to do is to try and perform a select on the TRACYSQL server which uses the synonym on ROBINSQL (if you can follow that. It’s a little circular, but the principle is what we’re after):

select * from RobinSQL.testDB.dbo.realTestTable

That’s the annoying error message because we definitely do have access to any objects (as it’s just a lab I’m using a sysadmin account on both servers and for the linked servers), and it definitely does exist. In fact we can prove it:

select * from RobinSQL.testDB.sys.synonyms

Therefore it’s there, we can access the system tables through the linked server which tells us the synonym exists, but SQL Server just isn’t allowed to see it directly. This is just a flaw in SQL Server as a whole and there’s nothing we can do about it.

However, if we use a view then we no longer have the problem. We can even cheat by placing a view on top of the synonym directly (although it would be better to go straight to the underlying linked server table… although this does at least cater for any table structure changes that might happen, as per my previous post):

create view testRealView as select * from testRealTable

And this works just fine:

Therefore another argument against the use of synonyms in a database… if you’re planning on having any application access your SQL Server via a linked server connection then they won’t be able to see or access your synonyms… instead you’ll have to use views.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.