Schema Separation in SQL Server 2005
I had some interesting discussion at my event in Elizabeth, NJ regarding how applications would work after the SQL2000 Server was upgraded to SQL Server 2005. I had explained that applications would still work, based on the fact that the object owner name in SQL 2000 becomes the Schema Name in SQL Server 2005. From the SQL Server 2005 CTP Books Online I found this. In case anyone hasn't noticed I get a lot of answers out of the online books. (Note to self: Make the books online required reading for everyone [;)] then again maybe not, because i'm not reading all of it!!!)
"In SQL Server 2000, an object's owner is effectively identical to the owner of the schema that contains it. So the "schema" of a fully-qualified name in SQL Server 2000 is also a user in the database. For this reason, prior to dropping a user from a database, the administrator needed to drop all the objects owned by that user or change their owner. Consider a SQL Server 2000 database containing this object:
This object is owned by user "george". If the administrator needs to delete user "george", he must first delete this object or change its owner. In the latter case, it might be called:
Transferring ownership of the object has changed its fully-qualified name. Any code that refers to "accounting.ap.george.reconciliation" must now be revised and tested, etc.
In SQL Server 2005 schemas exist independently of the database user that creates them. So fully-qualified object names now have four parts:
Objects are now contained within schemas that are entirely separate from database users. Ownership of schemas can be transferred without changing their names. Programs can refer to an object such as "accounting.ap.processes.reconciliation" – a name that need not change as users are added and dropped from the database."