SchemaName.ObjectName

Question

SchemaName.ObjectName

Rechana Rajan

SSCertifiable

Points: 7709
More actions
December 6, 2016 at 6:24 am

#316603

We have an app user with default schema 'bill' . The scripts given by developers never mention the schema name 'bill' before object name as its default for them . My question is will this cause performance issue?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

yes it could potentially cause problems. best practice would be to go ahead and schema-qualify all the scripts provided to avoid any issues.

The scenario i would worry about is if the app user gets access to the dbo schema, even though it's default schema is [bill], you could start getting object not found errors, because the table [invoices] is actually [bill].[invoices] but a query resolves to [dbo].[invoices], which doesn't exist.

Redgate's SQL Prompt has some nice features to schema-qualify and square bracket qualify scripts like that, so you can fix scripts really quickly.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 2

Lowell (12/6/2016)
yes it could potentially cause problems. best practice would be to go ahead and schema-qualify all the scripts provided to avoid any issues.
The scenario i would worry about is if the app user gets access to the dbo schema, even though it's default schema is [bill], you could start getting object not found errors, because the table [invoices] is actually [bill].[invoices] but a query resolves to [dbo].[invoices], which doesn't exist.
Redgate's SQL Prompt has some nice features to schema-qualify and square bracket qualify scripts like that, so you can fix scripts really quickly.

Thanks Lowell,

User have datareader and datawriter permission on DB and default schema is [bill]. As per my understanding for this user it will check the [bill] schema first and then dbo and it wont go any further. Please correct if I am wrong.

Thom A SSC Guru Points: 99126 More actions · Answer 3

Rechana Rajan (12/7/2016)
As per my understanding for this user it will check the [bill] schema first and then dbo and it wont go any further. Please correct if I am wrong.

No, it will check the default Schema only. If that default is [bill] then it will check for [bill].[tablename]. If [bill].[tablename] does not exist, then the failure occurs.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 4

Thom A - Wednesday, December 7, 2016 4:46 AM
Rechana Rajan (12/7/2016)
As per my understanding for this user it will check the [bill] schema first and then dbo and it wont go any further. Please correct if I am wrong.
No, it will check the default Schema only. If that default is [bill] then it will check for [bill].[tablename]. If [bill].[tablename] does not exist, then the failure occurs.

Thanks Thom