THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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?

Published Wednesday, June 01, 2011 7:58 AM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

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

June 1, 2011 2:45 AM
 

Uri Dimant said:

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

June 1, 2011 2:45 AM
 

Greg Low said:

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.

June 1, 2011 3:10 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement