SQL: DCL clauses in CREATE SCHEMA

Most people seem to be unaware that a CREATE SCHEMA statement can also include other DDL or DCL within it.

I’ve never been very keen on the option to add additional clauses in the CREATE SCHEMA statement as I’ve seen lots of issues when scripts are executed manually. You get a different outcome, depending upon how you execute it. For example, if you run statements in this script:

CREATE SCHEMA SomeSchema AUTHORIZATION Someone

CREATE TABLE Blah (Some table definition);

Where does the table Blah get created? Well, it depends.

If someone executes the first line on its own, then the second line, the table gets created in their default schema, whereas if they execute the statement as a whole (as it’s actually just one statement not two), the table gets created in the new schema. I consider this fragile code.

That’s bad enough, but the one that makes no sense to me is the DCL clause. If I change the above to:

CREATE SCHEMA SomeSchema AUTHORIZATION Someone

CREATE TABLE Blah (Some table definition)

GRANT SELECT TO Someoneelse;

You would think that the GRANT clause would only ever apply to the newly-created schema. If I executed the above statement, it would grant SELECT on the database instead. The Microsoft Learn entry for CREATE SCHEMA says:

grant_statement

Specifies a GRANT statement that grants permissions on any securable except the new schema.

I might not be thinking widely enough but what is the point of a GRANT statement that grants permissions on anything except the newly-created schema, when the clause is part of the CREATE SCHEMA statement?

It then seems even odder that the only example given in that Microsoft Learn page specifically grants permissions on the newly-created schema. Anyone know why this is designed this way?

Learn more about Advanced T-SQL

If you really want to learn about SQL Server Advanced T-SQL, we have an online on-demand course that you can enrol in, right now. You’ll find it at SQL Server Advanced T-SQL for Developers and DBAs

2026-02-15