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


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • 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.
Modification Type:MinorLast Reviewed:6/27/2006
Keywords:kbnofix kbdesign kbinfo kbtshoot KB918346 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.