Default Schema and Synonyms

database0.User1 has a default schema of database0.Schema1 and also belongs to database1.

I have the following synonyms in database0:

Schema1.Customers - points to database1.dbo.customers

dbo.Customers - point to database2.dbo.customers

User1 has execute rights in database0. I have a stored procedure in database0 that says

select * from Customers

I thought since User1's default schema was Schema1, it would automatically see select * from customers as select * from database1.dbo.customers. It keeps going to the dbo.Customers schema.

Isn't it supposed to go to the default schema first, then try dbo?

What schema is the stored procedure in database0 defined?|||DBO|||

In that case, the behavior you see is expected. sql server always resolves to the container schema first, if there is a container for the object.

In this case I believe the pseudo code for scenario looks like this.

create proc dbo.Proc1 {select from customers}

For the object customers, the container is Proc1. So the resolution for customers goes to the schema of the container procedure Proc1 which is dbo. If you define the procedure Proc1 in the schema Schema1, then the resolution for customers will go to the schema schema1.

hope that helps.

|||

Then we would have to have a separate set of SP's for all schemas. We were hoping to use the synonyms to dynamically point users to the correct database that contains their tables.

If we have to have separate SP's then we might as well store them along with each database. We were trying to have just one set of SP's that could "figure out" which database they needed to be in based on the schema the user belongs to.

Does that make sense?

|||

If a user connects to a database and just runs "select * from customers" then sql server resolves to the default schema of the user. So it will resolve to the synonym in the default schema of the user.

When you wrap that t-sql statement in a SP then the resolution changes to the container schema first.

I am not sure if this information is useful to you in solving this problem but I am posting it just in case.

|||

That won't work. Here is what we are trying to accomplish.

We have one database that will be our "controller" database. It will house all the SP's (all code). There will be multiple customer database that will have identical tables. Basically each customer will have their own database with their own data.

We want to controller database to figure out based on the user which customer database to use for their data. We were hoping the Synonyms linked to a schema would work for us. But we hit a road block with the SP belonging to dbo.

Do you see any solution for us? We would like to have one set of code and differents sets of databases for our customers.

|||

Inside the SP body make a call to the builtin SCHEMA_NAME() to figure out the default schema of the caller. Once you have the default schema of the caller you can use 2 part names to call the correct synonym from inside the procedure.

PSEUDO CODE:

create procedure p_Controller as

declare @.defaultschema sysname

SET @.defaultschema=SCHEMA_NAME()

EXEC ('SELECT * FROM ' + @.defaultschema + '.Customers')

--Calls the synonym in the default schema of the caller

go

Ensure that the right permissions are available on the procedure and the synonyms. Synonyms are separate object types themselves so you need to grant the correct permissions on them for users to be able to use them.

Hope this helps.

|||

I can see how that would work. But our performance will go way down when we go and update all stored procedures SELECT's to run inside EXEC(). We don't want to do that.

|||Unfortunately , I cannot think of any other way to achieve your objective without using dynamic sql.