How does a users DEFAULT_SCHEMA get set to something other than "dbo"

One of the settings for a SQL Server user is the DEFAULT_SCHEMA. This is frequently dbo but not always. I realize you can change the setting by using this code:

ALTER USER [UserName] WITH DEFAULT_SCHEMA = [SchemaName]

From what I can tell whenever I create a USER and don't specify a DEFAULT_SCHEMA it uses dbo. But I'm still finding USERS that have a different default schema.

If I remember correctly when you have an instance that is upgraded from SQL 2000 the default schema is the schema matching the users name. I'm guessing this is where most if not all of what I'm seeing comes from.

Are there any other ways that you can create a USER without specifying DEFAULT_SCHEMA and have it be created with something other than dbo?

Note: This is for versions of SQL Server 2005 and up.

One way this happens is by having a Login that has the CONTROL SERVER server-level permission (hence does not need an actual USER in order to access a database) but not in the sysadmin fixed server role (as then there is an implied default schema of dbo) create an object without explicitly specifying the schema to put it in.

According to the MSDN page for CREATE SCHEMA:


Implicit Schema and User Creation


In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

• A login has CONTROL SERVER privileges.

• A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

Hence, I am speaking of the first case noted above, and @Max's answer is about that second case.

The following is an example for the first case (please note that the following example uses tempdb since every instance has that, but I did test on a non-tempdb database to be sure). To see an example of the second case, please see @Max's answer (there is no need to duplicate that here).

USE [master];

CREATE LOGIN [DefaultSchemaTest] WITH PASSWORD = 'DefaultSchemaTest';
GRANT CONTROL SERVER TO [DefaultSchemaTest];
GO

EXECUTE AS LOGIN = 'DefaultSchemaTest';

USE [tempdb];
SELECT * FROM sys.database_principals WHERE [type] <> 'R';
SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

CREATE TABLE MySchemaTest (Col1 INT);

SELECT * FROM sys.database_principals WHERE [type] <> 'R';
SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

USE [master];
REVERT;


StackExchange Database Administrators Q#134105, answer score: 3