Default Schemas and Access

  • (also posted in 2005 admin)

    Hi,

    We have a MS Access .adp app which is having some security problems.  We have a windows global group which is a user - which is added to a role.  The developers are having problems unqualified sql statements trying to find user.table instead of dbo.table.  It seems we are not able to specify a default schema for the global 'user' - the option is greyed out and a sql statement fails.  MS says:

    Default Schemas

    A default schema is used to resolve the names of securables that are referred to without their fully qualified names. In SQL Server 2000, name resolution first checks the schema owned by the calling database user, followed by the schema owned by dbo.

    In SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.


    Is the default truly undefined in a global group user? 

    If so, I'm thinking that access is being screwy and is just adding the users id on the front of objects when a schema isn't specified.

     

    Thanks a bunch!

  • In all SQL always qualify the reference, ie. dbo.tablename.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply