| SteveM 2006-02-16, 9:23 am |
| Is there a best practices guide for schema management? Is it best to
remove objects from dbo?
My understanding is that upgrading a SQL Server 2000 database will
result in all objects being moved into a dbo schema.
However, I've read some posts here that this setup isn't necessarily
the best practice for establishing schemas. We would also like to
eliminate the need for database - level dbo's.
So, I'm thinking that once a database is converted, that I would create
a new schema "prod" and then create a schemaowners role. The old dbo's
would then be made members of that role and then removed from the old
dbo role.
I see that then I can alter a schema and transfer the dbo objects. Is
this done only at the catalog level and doesn't actually cause a
movement of data?
Once the objects are moved, permissions would be recreated and users
would have the new schema made their default schema.
Seems the only issue at this point should be any code explicitly
referring to dbo objects (i.e, "select * from dbo.xxxx).
Any issues with this? Or, not really necessary?
|