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

Leave a comment

June 22, 2020 by Kenneth Fisher

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 [SQL2019TestEnv\SevenDwarfs] FROM WINDOWS;
USE Test;
GO
CREATE USER [SQL2019TestEnv\SevenDwarfs] FROM LOGIN [SQL2019TestEnv\SevenDwarfs];
GRANT CREATE TABLE TO [SQL2019TestEnv\SevenDwarfs]; 
GRANT ALTER ON SCHEMA::dbo TO [SQL2019TestEnv\SevenDwarfs] ;
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 = 'SQL2019TestEnv\Doc';
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 = 'SQL2019TestEnv\Doc';
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 SQL2019TestEnv\SevenDwarfs. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,136 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: