Re: sql2k5 schema qualifier



Hi Dave

If you do not specify a schema name, SQL Server will look in your default
schema, then in the dbo schema.
This is very similar to what we happens in SQL Server 2000, in that
unqualified objects are first checked to see if they are owned by the
current user, then checked to see if they are owned by dbo.

Roles, permissions,etc do not help determine what schemas to check.

The reason is that there can be objects of the same name in different
schemas (or in SQL 2000, owned by different users).
What if MIS and MIS_TABLES both included a table named foo_bar? Which one
would you want?

Can you explain what behavior you saw in SQL 2000 that makes you say it was
easy?

--
HTH
Kalen Delaney, SQL Server MVP


"Dave Joyner" <d4ljoyn@xxxxxxxxx> wrote in message
news:%23hku59fkGHA.408@xxxxxxxxxxxxxxxxxxxxxxx
I need to make it so that a given user can access objects in two different
schemas without the qualifier

schemas

MIS
MIS_TABLES

user

addg (default schema MIS)

tables

MIS.foo
MIS_TABLES.foo_bar

now log in as addg

select * from foo (works)
select * from foo_bar (this doesn't work)
select * from mis_tables.foo_bar (works)

I can't figure out how to do this -- after trying adding roles and
altering users, etc., etc. seems like it was easy in 2k.

This has to do with external tools that embed the fully qualified object
name in everything.

Thank you for your time
Dave Joyner


.