default schema synonym

Question

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

I am also logged in as a user with BarSchema as 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 an error "Invalid object name 'FooDb..Tbl'" when executing

SELECT * FROM TblSynonym

If i change the synonym to

CREATE SYNONYM TblSynonym FOR FooDb.BarSchema.Tbl

it works fine.

Why doesn't the default schema work in synonyms?

(The background is that I'm consoldating data from several databases which all got same table names but different schema names. It would be a lot easier if I could set the default schema for each database on the user and then ignore it everywhere in the script)

Answer 1

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

schema_name_2 Is the name of the schema of the base object. If schema_name is not specified the default schema of the current user is used.

This works for me in SQL Server 2008:

create synonym TestSynonym for TestDB..TestTable

One cause might be that the default schema is associated with the user, not the database. Check if your user has an unexpected default schema? In my SSMS, that setting is located under Database -> Security -> Users -> Properties.