Default schema and Resolving table names in Stored Procs

[Stu]

Why can't my stored procedures resolve table names to the current user's
default schema?

I have so-called "multi-tenant" SQL Server 2005 system where
- each customer's data is in a separate schema.
- each customer has a SQL login/user.
- the customer's schema is set as their default schema

e.g.
CREATE SCHEMA [customer12] AUTHORIZATION [customer12User]
GO

ALTER USER [customer12User] WITH DEFAULT_SCHEMA=[customer12]
GO


- there is also a single shared schema containing common data and stored
procedures

So, say we have the following tables...
customer11.Payments
customer12.Payments

...and the following stored procedure
shared.GetPayments

Inside GetPayments I would expect to be able to use unqualified table names
so that the actual table would be resolved by looking in the default schema
of the executing user, e.g:

create procedure shared.GetPayments
as
select * from Payments

However, I get the error: Invalid object name 'Payments'.

To double-check that I am actually executing as a user with the default
schema I do the following:

alter procedure shared.GetPayments
as
select current_user + ' (in proc)'
select * from Payments


...and then I execute like this:

select current_user
exec shared.GetPayments

Results:

----------
customer12User

(1 row(s) affected)


----------
customer12User (in proc)

(1 row(s) affected)

Msg 208, Level 16, State 1, Procedure GetPayements, Line 4
Invalid object name 'Payments'.

So it is executing as the right user...why can't it resolve Payments to
customer12.Payments?

Many thanks

Stu

[Peter Yang [MSFT]]

Hello Stu,

I understand that you encounter "Invalid object name" when executing a SP
with a schema that is different from the user running the SP. The table
name cannot be resolved to the default schema of the running user properly.
If I'm off-base, please let me know.

I think this behavior is normal for a stored procedure. During the creation
of stored procedure, it's deferred name resolution, so it allow user to
create stored proc which references invalid object. During execution of the
stored proc, the referenced table will need to be schema qualified. If not,
the default to the stored proc owner.

See below SQL BOL topic "Qualifying Names Inside Stored Procedures": Inside
a stored procedure, object names used with statements (for example, SELECT
or INSERT) that are not schema-qualified default to the schema of the
stored procedure. If a user who creates a stored procedure does not qualify
the name of
the tables or views referenced in SELECT, INSERT, UPDATE, or DELETE
statements within the stored procedure, access to those tables through the
stored procedure is restricted by default to the creator of the procedure.


If you run the statments directly by the user, it shall resolve to the
default schema of the user. However, this is not true in a SP as described
above. If you have any comments or concerns on this, please feel free to
let's know.

In the meantime, I also encourage you submit via the link below. Thank you.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Stu]

Thanks Peter. I have submitted the feedback.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272964

[m***@gmail.com]

Post by Stu
Thanks Peter. I have submitted the feedback.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272964

It seems even in SQL 2012 version this has not changed. An easier workaround?

[Erland Sommarskog]

Post by m***@gmail.com

Post by Stu
Thanks Peter. I have submitted the feedback.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackI
D=272964

Post by m***@gmail.com
It seems even in SQL 2012 version this has not changed. An easier workaround?

The current behaviour is not going to change, as it would break backwards
compatibility.

What is asked for in the Connect item would require some new syntax; in
practice the Connect item asks for a "generics" feature. There would
be one query plan in the cache of the procedure for each schema. (And
these plans would necessarily not be identical, as each set of tables
has their own statistics.)

This situaton is not too difficult to deal with the current functionality.
You have one set of the procedures, but you deploy them multiple times to
the database, once per schema. When you deploy them you impersonate
the schema owner, which also has its own schema as the default schema.

Note that this has the advantage that different tenants can be on different
versions, because you can update them at different times.

[m***@gmail.com]

I understand it better now. Appreciate your response!

Thanks.

Mahesh

Post by Stu
Why can't my stored procedures resolve table names to the current user's
default schema?
I have so-called "multi-tenant" SQL Server 2005 system where
- each customer's data is in a separate schema.
- each customer has a SQL login/user.
- the customer's schema is set as their default schema
e.g.
CREATE SCHEMA [customer12] AUTHORIZATION [customer12User]
GO
ALTER USER [customer12User] WITH DEFAULT_SCHEMA=[customer12]
GO
- there is also a single shared schema containing common data and stored
procedures
So, say we have the following tables...
customer11.Payments
customer12.Payments
...and the following stored procedure
shared.GetPayments
Inside GetPayments I would expect to be able to use unqualified table names
so that the actual table would be resolved by looking in the default schema
create procedure shared.GetPayments
as
select * from Payments
However, I get the error: Invalid object name 'Payments'.
To double-check that I am actually executing as a user with the default
alter procedure shared.GetPayments
as
select current_user + ' (in proc)'
select * from Payments
select current_user
exec shared.GetPayments
----------
customer12User
(1 row(s) affected)
----------
customer12User (in proc)
(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure GetPayements, Line 4
Invalid object name 'Payments'.
So it is executing as the right user...why can't it resolve Payments to
customer12.Payments?
Many thanks
Stu