ddladmin can create tables but not schemas?

_{Saturday, July 15, 2017 12:11 AM}

We have a login used for our app "migrations" which seems to be a term of art for a way to deploy database changes, and we added "ddladmin" to it so it can create tables, and just for "fun" I also wanted to create a new schema to use for some admin purposes - but it seems I can't use the migration login to deploy the new schema.

I presume one needs to be dbo ... no, well, BOL says needs "create schema" permission.  OK.  Hmm, still nogo.

create schema foo authorization dbo;
go

The sticking point seems to be dbo?

Msg 15151, Level 16, State 1, Line 27
Cannot find the user 'dbo', because it does not exist or you do not have permission.

Hmm.  I want the dbo owner because, well, because I'm fuzzy on what happens otherwise, I had some trouble with this when I created a schema without specifying "authorization" in which case it defaults to me, and then when I copied the database from here to there and had to fixup the logins, well, something broke because of the ownership and non-existing login/user, whatever it was.

So is there yet another thing I can grant the migration account short of dbo, so it can create a schema authorized dbo?

(actually I don't have much concern granting the migration login dbo, but I guess we want to keep it as limited as possible)

Advice welcome,

Thanks,

Josh

_{Saturday, July 15, 2017 2:59 AM ✅Answered}

Hmm.  I want the dbo owner because, well, because I'm fuzzy on what happens otherwise, I had some trouble with this when I created a schema without specifying "authorization" in which case it defaults to me, and then when I copied the database from here to there and had to fixup the logins, well, something broke because of the ownership and non-existing login/user, whatever it was.

So is there yet another thing I can grant the migration account short of dbo, so it can create a schema authorized dbo?

In addition to CREATE SCHEMA, the database principal needs IMPERSONATE permissions on the principal specified in the AUTHORIZATION c;ause. This is mentioned in https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql. Granting impersonate on dbo is something that should be done with care but since db_ddladmin members already has considerable permissions, you are just a step or two away from db_owner anyway.

You could create a signed proc with impersonate permissions the ddl admin user could execute to create a dbo-owned schema to avoid direct dbo impersonation. Below is an example gleaned from the methods described in Erland's article (http://www.sommarskog.se/grantperm.html#Certificates). Note that the user could elevate permissions via ownership chaining to access data in other dbo-owned schemas but won't be able to modify dbo schema objects without control permissions on the schema. 

CREATE CERTIFICATE SchemaCreator
   ENCRYPTION BY PASSWORD = 'sd(K<B($$$)@#'
   WITH SUBJECT = 'Certificate to create dbo-owned objects',
   START_DATE = '20170101', EXPIRY_DATE = '20400101'
GO

CREATE USER SchemaCreator FROM CERTIFICATE SchemaCreator;
GRANT IMPERSONATE ON USER::dbo TO SchemaCreator;
GRANT CREATE SCHEMA TO SchemaCreator;
GO

CREATE PROC dbo.CreateSchemaWithAutorizationDbo
    @SchemaName sysname
AS
DECLARE @SQL nvarchar(MAX) = N'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + N' AUTHORIZATION dbo;';
EXEC(@SQL);
GO

ADD SIGNATURE TO dbo.CreateSchemaWithAutorizationDbo BY CERTIFICATE SchemaCreator
    WITH PASSWORD = 'sd(K<B($$$)@#';
GRANT EXECUTE ON dbo.CreateSchemaWithAutorizationDbo TO YourDdlUser;
GO

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

_{Saturday, July 15, 2017 9:05 AM}

Thanks Dan.

The signed proc is something I might explore as a way to install a back door, but you see I'd have the same problem installing that in the first place as I do with the schema in the first place - I only have automatic access to these remote databases under the current low permissions.

I guess this is just another big sigh - is there really such a difference in semantics between adding a new "schema" and adding a new table within a schema?  It's all rock and roll, excuse me, name space management, if you ask me.  Oh well.

Josh

_{Saturday, July 15, 2017 12:06 PM}

I guess this is just another big sigh - is there really such a difference in semantics between adding a new "schema" and adding a new table within a schema?  It's all rock and roll, excuse me, name space management, if you ask me.  Oh well.

A schema is nothing more than a namespace when everything is owned by dbo. Desired or not, the implication of different schema owners is it introduces a security boundary. Not only does the schema owner have CONTROL permissions over contained objects, different owners also break ownership chains between schemas. 

If you don't use the dbo schema for user objects, db_ddladmin members with CREATE SCHEMA could explicitly specify db_ddladmin as the owner of all user schemas so that schema name is effectively just a namespace. 

Dan Guzman, Data Platform MVP, http://www.dbdelta.com