I created a table and SQL created a schema and a user.

Demo first and then I’ll explain what happened.

First thing I did was run this script:

CreateUsers.bat

It’s a quick bat file that creates a couple of windows groups and a bunch of users for me to use with testing.

Next I created a login for the Dwarfs windows group and a user in the Test database with sufficient permissions to create a table (remember that you have to have ALTER on the schema you are adding the table to and for no particular reason I’m assuming that’s dbo).

CREATE LOGIN [SQL2019TestEnvSevenDwarfs] FROM WINDOWS;
USE Test;
GO
CREATE USER [SQL2019TestEnvSevenDwarfs] FROM LOGIN [SQL2019TestEnvSevenDwarfs];
GRANT CREATE TABLE TO [SQL2019TestEnvSevenDwarfs]; 
GRANT ALTER ON SCHEMA::dbo TO [SQL2019TestEnvSevenDwarfs] ;
GO

Next I’m going to impersonate Doc, revert and double check that in fact the only user in the database is dbo along with no user schemas. Basically I want to prove that the impersonate didn’t do anything.

EXECUTE AS USER = 'SQL2019TestEnvDoc';
REVERT;
SELECT * FROM sys.database_principals WHERE Type = 'U';
SELECT * FROM sys.schemas ORDER BY schema_id;
GO

And finally I’ll create my table.

EXECUTE AS USER = 'SQL2019TestEnvDoc';
CREATE TABLE DocsTable (Col1 INT);
REVERT;
SELECT * FROM sys.database_principals WHERE Type = 'U';
SELECT * FROM sys.schemas ORDER BY schema_id;
GO

And there you go. A brand new user and schema both named after the user that created the table. I also want to point out that the user who created the table did not have the permission to create a new schema.

So what’s going on?

Doc is connecting to the Test database using the AD group SQL2019TestEnvSevenDwarfs. However, when I created the user for that AD group I didn’t specify a default schema, and if you look in sys.database_principals for this user the default_schema_name is set to NULL. Which of course means that Doc isn’t using a default schema. On top of that I didn’t specify a schema in the CREATE TABLE command. So what schema should SQL use? Well, a person might assume dbo since it’s the most common schema and probably what was intended. SQL of course isn’t going to make that type of assumption. Instead, it created a schema using the name of the user, then created a user with that as the default schema. Now the user has a default schema and the create can proceed.

I’ll admit I’m making some assumptions myself here since I didn’t actually check the log to see exactly what happened.

Also, no need to worry about the new user that was created. It has absolutely no permissions. Not even CONNECT that is usually added by default.

Now, there is an easy fix to this. If you set the default schema for the AD group to say dbo then objects created without a schema specified will just be created under the schema you specified. I would say that you could just make sure you include the schema when you create objects but knowing myself, and having worked with enough other people just like me (in this specific respect), I just don’t see that as a realistic fix.