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:
CREATE SCHEMA SomeSchema AUTHORIZATION Someone
CREATE TABLE Blah (Some table definition);
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, the table gets created in the new schema. 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 BOL entry: http://msdn.microsoft.com/en-us/library/ms189462.aspx
makes even less sense to me. It says
“grant_statement
Specifies a GRANT statement that grants permissions on any
securable except the new schema.
”.
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 BOL specifically grants permissions on the
newly-created schema. Anyone know why this is designed this way?