Re: Default schema not working properly for AD groups

According to SQL 2005 BOL, if a database user is mapped to an AD group, the
default schema cannot be set and it will default to dbo.

I think you are referring to the CREATE USER topic in the Books Online:

<Excerpt>

If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema. DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database. Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created. DEFAULT_SCHEMA cannot be specified for a user that is mapped to a Windows group, a certificate, or an asymmetric key.

</Excerpt>

Note that the doc mentions the user mapped to the Windows group but doesn't really say anything about the group members.

Is this problem addressed in some hotfix or is the product working as
designed? Any ideas?

This is by design. The explanation can be found under the User_Schema Separation topic:

<Excerpt>

Users connecting through a Windows-authenticated group will not have a default schema association. If such a user creates an object that is not qualified with a schema, a new schema is created, its name is set to the current user's name, and the table object is created in this new user-named namespace.

</Excerpt>


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Camilo" <Camilo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F66B31E7-B929-4BD5-8BA2-A209CD03DB5A@xxxxxxxxxxxxxxxx
I have a customer that is trying to use AD groups to manage permissions for a
SharePoint solution.

According to SQL 2005 BOL, if a database user is mapped to an AD group, the
default schema cannot be set and it will default to dbo.

We have done several tests and this is NOT the case. What really happens is
that a new schema is created for every member (account) of the group that
logs in and that schema becomes the default schema for each user.

We are running SQL 2005 SP2.

Is this problem addressed in some hotfix or is the product working as
designed? Any ideas?

Thanks!

Camilo


.

Relevant Pages

  • Re: Database connection issue using SQL schema user account
    ... They can simplify permissions insofar as being able to have ... new objects created inside of a schema inherit permissions assigned to ... --The dbo user account is not the same thing as the dbo default ... The dbo user maps to db_owner/sysadmin. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Alter User for domain user
    ... connect to the server with Windows Authentication, the default schema ... (I used to have the dbo role, ... role on the server. ... schema different from dbo for a Windows login. ...
    (microsoft.public.sqlserver.security)
  • Re: sql server
    ... managment studio is connected to a sql server 2000 standard edition database. ... the default schema name for me on sql server 2005 management studio ... Assuming your database is using objects only in the dbo schema here is what ...
    (microsoft.public.sqlserver.setup)
  • Re: Stored Procedure Disappearing
    ... Did you use dbo. ... Qualifier to dbo or if you have used something else for the schema (owner) ... summary report which obtains data from a large stored procedure containing ...
    (microsoft.public.access.reports)
  • Re: creating tabes with full schema name
    ... Yeah, ADP has a lot of trouble with full schema name, especially when it's ... not dbo by default ). ... On forms, you can set the Record Source Qualifier and for control, you can ...
    (microsoft.public.access.adp.sqlserver)