SQL 2008 R2 creates user/schema when Windows user creates tables

We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:

USE master
go
CREATE LOGIN [DOMAIN\AppUsers] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
USE AppDb
go
CREATE USER [DOMAIN\AppUsers] FOR LOGIN 
[DOMAIN\AppUsers]
go
EXEC sp_addrolemember N'db_owner', N'DOMAIN\AppUsers'
go

When the DOMAIN\User1 account logs onto the app, User1 queries tables in the dbo schema just fine because User1 is a member of DOMAIN\AppUsers, but this app allows the user to create tables too. When creating these tables without specifying a schema, SQL Server does the following:

• Creates a 'DOMAIN\User1' user in AppDb that uses a 'DOMAIN\User1' login not listed in SSMS\Security\Logins for the instance.

• Creates a 'DOMAIN\User1' schema in AppDb.

• Creates those tables using in the new 'DOMAIN\User1' schema.

I am completely baffled by these results. Here are my questions:

• I would expect the table creation to fail rather than create additional objects. Can someone point me to the part of Books Online that explains this?

• Why doesn't the server create a 'DOMAIN\AppUsers' schema and add the new tables to that schema if it is going to add schemas?

• Also, how does the database use a login not shown in SSMS\Security\Logins?

• Looking at the 'DOMAIN\User1' user in SSMS\Databases\AppDb\Security\Users, the user icon has a small red arrow pointing down. What does that mean?

We are just starting to use Windows Authentication within an organization that preferred SQL Authentication for simplicity, so I am sure my question comes from being ignorant of the differences. This code was written long before we considered using Windows Authentication, so I sure we need to improve our understanding of creating new schemas when logged on using Windows Authentication as anyone other than the database owner.

In case you can't tell, I am the one pushing

This has always happened, back to SQL Server 2000.

Without schema, how does SQL Server know you want to put it in the dbo schema?

The only way to specify a default schema is to :

• use SQL logins (not Windows)

• run as "sysadmin"

Neither of these is acceptable

Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.

Also, deliberate schema use is better for SQL Server 2005:

• tables in Data

• other tables in Archive, Staging etc

• code in schemas per client permissions: Desktop, WebGUI etc

Using the dbo schema is so last millenium :-) Links:

• Schema design - best practices?

• https://dba.stackexchange.com/q/7257/630

StackExchange Database Administrators Q#8318, answer score: 9