3

I am using some old databases so I have work with different servers.

I have created Synonyms on 'A' server and I able to do some operation on that without any problem. Now I want to use it in 'B' server, but it will now allow me access that synonyms in server 'B'. I have given all the access for that server.

Even I able to access the same table which I have created synonyms.

Snippet : On Server 'A' : CREATE SYNONYM [dbo].[EmployeeDB] FOR dbo.Employee

On Server 'B' ; Working with table name : select * from [B].[databaseName].[dbo].[Employee]

Not Working using synonyms name : select * from [B].[databaseName].[dbo].[EmployeeDB]

Ravindra Sinare
  • 675
  • 1
  • 9
  • 25

1 Answers1

3

No: You cannot reference a synonym that is located on a linked server. https://msdn.microsoft.com/en-us/library/ms187552.aspx

It is probably better to go the other way around (ie. you create a SYNONYM on B which is linked to A) You could also use a view if you want to hide dbo.Employee.

Julien Vavasseur
  • 3,854
  • 1
  • 20
  • 29
  • What is the drawback for using a view? Should synonyms even exist if view can handle everything a synonym does, but even more than that? – Tigerjz32 Oct 19 '18 at 19:19
  • @Tigerjz32 synonyms are alias for server objects, including stored procedures, functions, etc. A drawback would be for instance if you change the reference object but not the view. – Bernardo Dal Corno Oct 17 '19 at 19:34
  • `select * from reference.object` even if this is the definition for my view? – Tigerjz32 Oct 19 '19 at 03:27