Microsoft KB Archive/918346

= The user is not assigned to a default schema when you use Windows authentication to connect the user to SQL Server 2005 =

Article ID: 918346

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise X64 Edition

-



Bug #: 420094 (SQLBUDT)

Bug #: 20010701 (SQLBUDT)

Bug #: 20010689 (SQLBUDT)



SYMPTOMS
When you use Windows authentication to connect a user to Microsoft SQL Server 2005, the user is not assigned to a default schema. This issue occurs when the following conditions are true:
 * The individual user belongs to one of the following sets of users:
 * Windows domain users
 * A local Windows group
 * Permissions have been assigned to the Windows domain or to the local Windows group.
 * Permissions have not been assigned to the account of the individual user.



CAUSE
This issue occurs because you cannot assign a default schema in SQL Server 2005 to a Windows-authenticated set of users.



STATUS
This behavior is by design.



MORE INFORMATION
One of the consequences of this SQL Server 2005 behavior is that many schemas are unintentionally created in a database. Consider the following scenario:
 * A user is authenticated as a member of a Windows domain. Then, the user connects to a SQL Server 2005 database.

In this example, MYDOMAIN\Fred is authenticated as a member of the Windows domain MYDOMAIN\Developers.
 * MYDOMAIN\Fred connects to the SQL Server 2005 database MYDATABASE.
 * The user has permission to run CREATE statements.
 * The user creates an object.

In this example, MYDOMAIN\Fred creates a table object that is named TABLE1.
 * A new schema has now been unintentionally created.

In this example, a schema that is named MYDOMAIN\Fred is created when the TABLE1 table object is created. The TABLE1 table object is located in the MYDOMAIN\Fred schema.

Keywords: kbinfo kbtshoot kbnofix kbdesign KB918346

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.