Can a synonym reference an object in another database

This is an interview question. Can we refer synonym in another database or another server. Answer is yes. Only we cannot refer on Azure SQL DB. A synonym in SQL Server can reference objects in another database or even on a linked server by using three-part or four-part naming. The synonym simply stores the object name string and resolves it at runtime, with permissions enforced on the base object.

The 3-part or 4-part name as plain text

Ø  Database.Schema.Object

Ø  Server.Database.Schema.Object

See the demo

use Test_DB go CREATE TABLE dbo.Orders (     OrderID INT,     Amount  INT ); GO  insert into dbo.Orders(OrderID,Amount) values (1,20),(2,25),(3,35),(4,45)

Table created on the TEST_DB. Now we are creating synonym in another Database.

USE AdventureWorks2019; GO  CREATE SYNONYM syn_Orders FOR Test_DB.dbo.Orders; GO

Now see the data

User must have permission on the base object in other database. Synonym itself doesn’t grant access.