Create Table so that user is the owner

Question

It is my understanding that the default behavior when creating a table in SQL 2005 is that it will be created with dbo as the owner of the table. Is there a way to change this default behavior so that all tables get created as the user instead of as dbo? I am working on moving an application from SQL 2000 to SQL 2005 and much of the logic within the application makes the assumption that the default behavior is to create a table with the user as the owner.

Answer 1

You can first create the schema (that you, or the target user, own), then create the table(s) under the schema, ala:

CREATE TABLE [yourSchema].[sales](...)

The schema / owner situation are different in sql2005.

Whats nice is that the schema name doesn't have to the same as the owner name. And if the current schema owner ever leaves you can reassign the schema ownership to someone else.