access tables without schema

Hello

in my database I have schema "x". I have user "x" who is database owner, The user has schema "x" as default schema and is owner of this schema.

Now there are tables "x.mytable"

If I connect to the db as "x" and try

select * from mytable

I get an error "Invalid object name", I have to write it this way

select * from x.mytable

Why?

Thank you

Eckard

You need to check the metadata for these tables and see what might be wrong. Here is a script that I put together to demonstrate that it works, with a few select statements peppered in that you can use to check your tables:

use tempdb
go
create user bob without login
go
grant create table to bob
go
create schema bob
go
ALTER AUTHORIZATION ON schema::bob TO bob
go
ALTER USER bob WITH DEFAULT_SCHEMA = bob
GO
--check defaults
select name,default_schema_name, type_desc
from sys.database_principals
where name = 'bob'
go
--Then switch context to executing as 'bob'
execute as user = 'bob'
go
--let him (er it) create a table, so it is in the schema
create table bob.tableName
(
tableNameId int
)
create table tableName2
(
tableNameId int
)

go
select name, schema_name(schema_id)
from sys.objects where name in( 'tableName2', 'tablename')
go
select *
from tableName
select *
from tableName2
go
revert

|||

Regarding I have user "x" who is database owner,

All users belonging to the database role db_owner will create by default objects in the dbo schema. So if you make a login X as the owner of a database, and then connect as that login to the database, the default schema for that user is the dbo schema. My guess would be that this is the problem you are running into. Instead of making the login 'X' , the owner of the database, why not grant him CONTROL permission on the database ?

|||

Thank you Louis, Asvin but it is still not working for me:

The user is named "gwg01", the table name is "caddr" and the dbname is "gwgdaten". I'm connected as "gwg01" when I execute this script

use gwgdaten

go

sp_change_users_login 'auto_fix', 'gwg01'

go

grant control to gwg01

go

create schema gwg01

go

ALTER AUTHORIZATION ON schema::gwg01 TO gwg01

go

ALTER USER gwg01 WITH DEFAULT_SCHEMA = gwg01

GO

select name,default_schema_name, type_desc

from sys.database_principals

where name = 'gwg01'

>>>> gwg01 gwg01 SQL_USER

select name, schema_name(schema_id)

from sys.objects where name in( 'CADDR')

>>>> CADDR gwg01

select * from caddr

>>>> Invalid object name 'caddr'.

Any ideas?

Thank you

Eckard

|||

Hi again

after I removed the user and created it new, everything works...

Eckard