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


.

Relevant Pages

  • 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 ...
    (microsoft.public.sqlserver.security)
  • 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' ...
    (microsoft.public.sqlserver.security)
  • Re: Model Driven Architecture, bussiness rules in DB?
    ... authorization schema. ... neither does SQL Server. ... For instance when you grant access to a table you should be able ... accessing applications? ...
    (comp.databases.ingres)
  • Re: SQL Server to .xsd
    ... > output an XSD from an entire database schema, ... > "Val Mazur" wrote: ... >> Do you need to get schema of specific table in a SQL Server? ...
    (microsoft.public.data.ado)
  • Re: How to access SQL Server 2005 with Query Analyzer (SQL 2000)
    ... Query Analyzer does not fully support new SQL Server 2005 features. ... it does not recognize schema names in the Object Browser. ... > SQL Server databases use the concept of schemas. ...
    (microsoft.public.sqlserver.clients)