New DDL

SQL Server 2005 gives you several new statements for working with schemas. Rather than take up space giving full syntax details, I'll briefly describe the statements, and when you get SQL Server 2005 installed, you can check the new Books Online (BOL) for details.

With CREATE SCHEMA, you can create a new schema and optionally use the AUTHORIZATION clause to specify a schema owner. If you don't specify AUTHORIZATION, the user running the statement will own the schema. The owner can own other schemas as well and might have a different default schema than any that he or she owns.

The ALTER SCHEMA statement lets the user reset the schema owner. (Only one principal can own a schema at a time, but the principal can be a SQL Server user, a Windows User, a Windows Group, or a SQL Server role.) Only a user with TAKE OWNERSHIP permission on the schema can take ownership of the schema. A member of the db_owner role can change schema ownership.

DROP SCHEMA succeeds only if the schema doesn't contain any objects. If the schema contains any objects, the drop will fail.

In addition, SQL Server 2005 has new Data Definition Language (DDL) commands for working with users. These follow a new standard of using CREATE and ALTER statements rather than stored procedures. CREATE USER lets you specify a username and optionally a login name to associate with the user. You can also specify a default schema. ALTER USER lets you change either the username or the user's default schema.