Article ID: 918346 - Last Review: November 20, 2007 - Revision: 1.3

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

View products that this article applies to.

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.

Back to the top

CAUSE

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

Back to the top

STATUS

This behavior is by design.

Back to the top

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.

Back to the top