User can't select from table using one part name

Question

Post reply

User can't select from table using one part name

Adam McArdle

Mr or Mrs. 500

Points: 540
More actions
April 20, 2009 at 7:24 am

#203185

I'm haivng a wierd problem on a colleague's server whereby a user can't select from tables using one part names.
I have a login, user and schema all called 'abc'.
If I log in as 'abc', running this SQL gives an error:
[font="Courier New"]SELECT TOP 1 * FROM Request
Invalid object name 'Request'.[/font]
However changing the SQL to
[font="Courier New"]SELECT TOP 1 * FROM abc.Request[/font]
works fine.
I've restored a backup onto my server and it's working fine. Is there any server option which disallows one-part references?
I know that it's best practice to include the schema name, but at the moment that's not an option.
I've tried everything I can think of, but I've got nowhere. Any help would be much appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

What is the default schema for the user "abc"?

[font="Verdana"]Markus Bohse[/font]

Adam McArdle Mr or Mrs. 500 Points: 540 More actions · Answer 2

Thanks for the quick repsone. The default schema is set to 'abc'. I've tried changing that to dbo and back, to no avail. Similarly dropping and recreating the user didn't help.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

So there is a table, abc.Request, and a user "abc", and you are sure the user has rights to the schema and/or table?

Maybe some other rights issue through group membership that the user is getting caught in?

Is user_name(), correct?

Adam McArdle Mr or Mrs. 500 Points: 540 More actions · Answer 4

interestingly user_name() is returning as 'dbo', not 'abc' as I would have expected. I'll have a dig and see what's going on.

Thanks Steve

Adam McArdle Mr or Mrs. 500 Points: 540 More actions · Answer 5

Problem solved. The abc login was part of the 'sysadmin' role. Once I've taken it out of that role everything's working fine.

Thanks again for the help.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Glad I could give a suggestion. Let us know what you find out.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

That makes sense - since, as a sysadmin the default schema is overwritten to 'dbo'. SQL Server checks the default schema (now dbo because of sysadmin), then checks the 'dbo' schema and neither schema contains the object.

One thing you should note is that it is now recommended that you always schema qualify your objects. One, it avoids issues like this - and two, it avoids multiple cached plans for the same query.

Without schema qualifying the object, if we have UserA and UserB with the same default schema, and referencing the object without schema qualifying the object you will have a cached plan for UserA and a cached plan for UserB. Could potentially become a large issue if you have lots of users.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs