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

Question

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:

1. Creates a 'DOMAIN\User1' user in AppDb that uses a 'DOMAIN\User1' login not listed in SSMS\Security\Logins for the instance.
2. Creates a 'DOMAIN\User1' schema in AppDb.
3. Creates those tables using in the new 'DOMAIN\User1' schema.

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

1. 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?
2. 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?
3. Also, how does the database use a login not shown in SSMS\Security\Logins?
4. 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 for the use of Windows Authentication over SQL Authentication. If we don't get to a solid understanding of this, we will revert back to SQL Authentication.

Answer 1

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

Answer 2

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).

1. The behaviour is documented in https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql, in the section "Implicit Schema and User Creation"

2. 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.

3. It's not shown simply because there is no login for that user. As specified in https://learn.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.

4. 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).

Answer 3

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.

Answer 4

Whilst this is an old question I have observed this behaviour (In SQL Server 2014) and found the following:

Given the script

USE [master]
CREATE DATABASE [Test]

USE [Test]

-- Note that we create the users without specifying the default schema
CREATE USER [MyDomain\MyADGroup] FOR LOGIN [MyDomain\MyADGroup] -- ad group
CREATE USER [MyDomain\MyDomainUser] FOR LOGIN [MyDomain\MyDomainUser] -- ad user (not in said AD group)

ALTER ROLE db_ddladmin ADD MEMBER [MyDomain\MyADGroup]
ALTER ROLE db_ddladmin ADD MEMBER [MyDomain\MyDomainUser]

EXECUTE AS LOGIN = 'MyDomain\MyAdGroupUser' -- a windows account which is a member of [MyDomain\MyADGroup]

CREATE TABLE Test
(
    a INT
)

REVERT 

EXECUTE AS USER = 'MyDomain\MyDomainUser'

CREATE TABLE Test
(
    a INT
)

This script will create two tables called Test.

The first CREATE TABLE creates a table called [MyDomain\MyAdGroupUser].[Test] and also creates the MyDomain\MyAdGroupUser schema (as well as a MyDomain\MyAdGroupUser database user which is disabled)

the second CREATE TABLE creates a table called [dbo].[Test]

The reason for this is that as the CREATE TABLE commands do not explicity state the schema, they will use the default schema.

As we did not specify the default schema when creating the users, SQL Server sets the Windows user's default schema as dbo but does NOT set ANY default schema for the Windows Group user and therefore this causes the schema / user creation