DCL clauses in CREATE SCHEMA

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?

3 thoughts on “DCL clauses in CREATE SCHEMA”

  1. Hi Greg,
    Should CREATE TABLE permission be granted?
    CREATE LOGIN test_login WITH PASSWORD = 'jj'
    CREATE USER test_login
    FOR LOGIN test_login WITH DEFAULT_SCHEMA = test_loginSchema
    CREATE SCHEMA test_loginSchema AUTHORIZATION test_login
    EXECUTE AS User='test_login'
    CREATE TABLE test_loginSchema.tb(id int)
    –Msg 262, Level 14, State 1, Line 1
    –CREATE TABLE permission denied in database 'db1'.

  2. Hi Greg,
    Should CREATE TABLE permission be granted?
    CREATE LOGIN test_login WITH PASSWORD = 'jj'
    CREATE USER test_login
    FOR LOGIN test_login WITH DEFAULT_SCHEMA = test_loginSchema
    CREATE SCHEMA test_loginSchema AUTHORIZATION test_login
    EXECUTE AS User='test_login'
    CREATE TABLE test_loginSchema.tb(id int)
    –Msg 262, Level 14, State 1, Line 1
    –CREATE TABLE permission denied in database 'db1'.

  3. Hi Uri,
    Not sure I follow the question. I'm talking about the clauses within the CREATE SCHEMA statement, not separate CREATE TABLE or GRANT statements.

Leave a Reply

Your email address will not be published. Required fields are marked *