SQL 2008 R2 creates user/schema when Windows user creates tables
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,Stagingetc - code in schemas per client permissions:
Desktop,WebGUIetc
Using the dbo schema is so last millenium :-) Links:
- Schema design - best practices?
- https://dba.stackexchange.com/q/7257/630
Well @gbn types faster than I do...
My only offering is...You reference the user is logging into the app and it allows the user to create tables and such. If the application is allowing this and the user is not doing this through SQL Server itself (logging directly into the database using SSMS) then you will need to check with the vendor of that application.
Although the question is very old and it already has an accepted answer, I will try to answer your questions more specifically (rather than giving general advice).
The behaviour is documented in https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql, in the section "Implicit Schema and User Creation"
If you want the objects to be created in a particular schema (when the schema is not specified explicitly in the statement), you should specify the DEFAULT_SCHEMA for the user. In SQL Server 2008 R2 (and earlier versions), you would get an error if you try to assign a DEFAULT_SCHEMA to a user based on a Windows group, but in SQL Server 2012 (and later versions) this is now possible.
It's not shown simply because there is no login for that user. As specified in https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql, a user may be created for someone who logs in using a different group or even without any login.
The small red arrow (or the small red x in the newer versions of SSMS) represents a user who does not have the CONNECT permission in the database (however, this permission may be obtained through another group).