CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicely
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?
Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus
|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:

> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>
|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me to
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
[vbcol=seagreen]
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREATE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
> make sense to me either, but since SQL2K5 mentioned successful completion I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the database
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote: