Friday, February 24, 2012

Global Groups, Schema and MS Acess

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 with 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? We are not totally locked in to the global group solution, but it does make admin easier for us.

Thanks a bunch!

You should create database roles for your users in SQL Server and then place your users into them. It may make admin easier to have a large group, but it is far safer to be able to track them.|||

Windows groups cannot be assigned default schemas. This is the object of many requests and makes the subject of the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1

Thanks
Laurentiu

No comments:

Post a Comment