Article ID: 918346 - Last Review: April 7, 2010 - Revision: 2.0

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

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

Bug #: 420094 (SQLBUDT)
Bug #: 20010701 (SQLBUDT)
Bug #: 20010689 (SQLBUDT)

Expand all | Collapse all

SYMPTOMS

When you use Windows authentication to connect a user to Microsoft SQL Server 2005 or to SQL Server 2008, 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 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 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