December 2, 2010 at 4:22 am
Hello,
I recently checked out the schemas on a couple of databases that had been migrated from SQL 2000 and saw that there was a schema named after every user of a database. Is this a legacy SQL 2000 feature? Could somebody tell me if it is good practice to reduce the number of schemas and get all the users under one Schema or is it not as black and white as that?
Thanks in advance for any help or advice,
D.
December 2, 2010 at 12:57 pm
It's not as black and white as that.
I do have an issue with SQL creating schemas for every user, and I tend to delete them and set all users default schema to DBO. Schemas have there place if you want to manage access to data and objects, but I feel they should only be used where they are needed. I've seen far too many databases where there are multiple objects with the same nme created by different users, each under their own schema. Resolving the schema mess can be a nightmare.
I would decide which schemas you need, and delete the rest. You need to make sure there are no objects owned by the schema, change the login to use DBO, then delete the schema.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
December 3, 2010 at 4:28 am
During the upgrade process, SQL Server 2k5 automatically creates a schema for every user. It's a "feature" that Microsoft put in for who knows what reason. Schemas are new to 2k5, replacing the old 2000 owner concept. So maybe MS thought everyone would want their lives made easier by having the schemas auto-created instead of having to go in and create them themselves.
We don't use schemas a whole lot at my workplace. One of the first things we did was delete most of the user related schemas because they were causing too many problems for us. But whether you keep them or not is up to you. Just remember, if you do keep them, everyone needs to use the schema.tablename naming convention or your life will rapidly become difficult.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply