My Place
The Sahara of blogs…

Here it is 2012 already and I am just starting to play with schemas in SQL 2005. Anyway, follow the directions below to just start using a Schema as a container or namespace for your objects.

1. Create a Role “schema_owner” and set the owner to dbo.
2. Create schema “myschema” and set the owner to “schema_owner”.  I personally used the same word for both the schema and the role. ex: Role=”myschema”; Schema=”myschema”.

Now, logged in as SA with default permissions:

• I can create, alter, select, etc. any objects to the new schema as long as a schema qualify my objects.
• Red-Gate’s SQL Prompt can ‘see’ the schema and related objects for proper prompting.

At first I started with only a schema that was owned by “dbo”.  I could create tables and query tables, but SQL Prompt would not suggest those objects.  After playing around, I tried duplicating one of the other default schemas like db_accessadmin or db_owner.  Once the shema is owned by the role and the role is owned by dbo, then SQL Prompt works as expected.

Bonus:   Name Resolution according to Bob Beauchemin at http://www.sqlskills.com/BLOGS/BOBB/post/Dude-wheres-my-table.aspx

1. sys schema
2. user’s default schema or the stored procedure schema
3. dbo schema

Go check out Bob’s post as he has several posts related to schemas that are very informative.

Also, if you havn’t heard of SQL Prompt, you can learn more here.