Any SQL DBA's out there?

[impalanar]

I have run into an issue where I think my DB schema has been damaged or something.

I am logged in as the schema owner (with this schema set to the user's default)

If I run the following query:

Select * from Tablename

I get the following result:

Invalid object name 'Tablename'

If I run it as:

Select * from Schema.Tablename

It works.

Unfortunately, the software I use relies on not having to preface every table with the Schema.

Ideas?

TIA

[MrBlah]

I'm not real familiar with sql server, but typically you have to log in with that schema/tablespace set as the default, or select it when you log in

is there any way to examine what sql server thinks your default schema is once your logged in? It sounds like it's not picking it up as the default, or it's barfing reading environment variables before that gets set

I would alter user and reset the default again just to be sure

and watch out for this, if someone gave the user that the software is using sysadmin rights

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

[SlimDizzleATL]

This is from my very limited knowledge of SQL, but did you try to reload the schema? That error indicates, to me, that the schema isnt set as the one your working with in the project. Since you said it may be corrupt. Just my limited opinion...

[impalanar]

and watch out for this, if someone gave the user that the software is using sysadmin rights

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

We have a winner, this is exactly what happened. I have been beating my head against my desk for hours, everything looked right but it just didn't work.

Thank you!

Thank you!

Thank you!

[impalanar]

Mr. Blah,

I owe you a lunch. I started looking at this issue shortly after 3 this afternoon and apart from a meeting and dinner, it is all I have done.