Hi Steve

Good point to bring up actually.

This sort of concept comes from the oracle world, but were see a clearer distinction between "user" and "owner" of objects. Here in oracle an instance = a single db with 1 or more "schemas", where schemas can be similar to a single sql server instance with a bunch of user databases. This is purely thinking aloud though and there are other issues to consider of course..

In SQL Server best practice stats "all objects owned by dbo"... so other "schema" users are irrelevant. I tend to disagree, esp during development where people can play around with their own objects and we cut them over to dbo and wrapper the security model around them to formalise the structure/framework of the user objects in the db.

There are some other cases where I like to break the dbo rule, and have a different schema owner for my audit tables.. eg:

dbo.course

dbaudit.course_audit

why? I can still wrapper up all my security privs etc, but at the same time its easily to single out my audit tables and keep them alphabetically listed. Of course, alphabetic listing is not the only reason... 🙂

The BIG problem with non-dbo schemas tends to be db import/export via DTS and its inability to match up obj names between schema owners.

Cheers

Ck