Accessing Synonyms Via Linked Server
Published: 24th July 2015
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
go

create database testDB
go

use testDB
go

create table testRealTable
(
  
id int identity
)
go

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
go

create database testDB
go

use testDB
go

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.
Comments:
NB: Comments will only appear once they have been moderated.