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?
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'.
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'.
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.