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

Kalen Delaney, SQL Server MVP

"Dave Joyner" <d4ljoyn@xxxxxxxxx> wrote in message
I need to make it so that a given user can access objects in two different
schemas without the qualifier




addg (default schema MIS)


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


Relevant Pages

  • Re: multiple cascade paths
    ... You should not have them if your schema is designed properly. ... make you understand that I tried to show that a proper design can have ... am a SQL Server MVP, for crying out loud - you can safely assume that I ...
  • Re: Grant Create View Ability Without Over Permissioning
    ... We are on MS SQL Server 2005 and use an ERP system that locks the ... All objects are part of the dbo schema. ... permission to use it. ...
  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
  • Re: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...