Default Schema Synonym

Question

I have a FooDb database with a BarSchema schema that contains a Tbl table (i.e. FooDb.BarSchema.Tbl )

I also registered as a user with BarSchema by default.

This query works fine

 SELECT * FROM FooDb..Tbl 

I also have a synonym for this table in another db

 CREATE SYNONYM TblSynonym FOR FooDb..Tbl 

But now I get the error "Invalid object name" FooDb..Tbl " on execution

 SELECT * FROM TblSynonym 

If I changed the synonym to

 CREATE SYNONYM TblSynonym FOR FooDb.BarSchema.Tbl 

It works great.

Why doesn't the default syntax scheme work?

(The background is that I am collecting data from several databases, all of which have the same table names but different schema names. It would be much easier if I could set the default schema for each database for the user, and then ignore it everywhere in the script)

Answer 1

The documentation suggests that the db..tbl syntax should work:

schema_name_2 Is the name the schema of the base object. If no schema_name is specified, the default scheme of the current user b.

This works for me in SQL Server 2008:

 create synonym TestSynonym for TestDB..TestTable 

One reason might be that the default schema is associated with the user, not the database. Check if the user has an unexpected default scheme? In my SSMS, this setting is in the database → Security → Users → Properties.