Today we have a guest editorial as Steve is out of the office.
I assume we are all familiar with dbo. in front of table names and all the other database objects. This is the default schema in SQL server. I would guess many of you use custom schemas with your systems, but perhaps some of you do not. A custom schema will put a different prefix in front of your database objects. I think custom schemas are incredibly useful and should be used as much as possible.
One benefit of a custom schema is the way it groups object and tables together. Often we have many different systems all living on the same database. In this case segregating systems by unique schemas can really help with organization of your database objects. It becomes very clear which tables, stored procedures, functions, views, etc. belong to which system. It can really cut down on the confusion we sometimes run into wondering which systems use which objects.
Even if you only have one system on your database it makes sense to have a custom schema because of security. Granting security is one of my favorite things to do for a system with a custom schema that forces all database access via stored procedures. When an AD group needs to access the system all I have to do is:
GRANT EXECUTE ON SCHEMA :: CustomSchemaName TO ADGroupName
I don’t have to give datareader or datawriter access to anyone. I don’t have to give DBO role membership, (I would not suggest that anyway). I just grant execute rights on the schema to the AD Group. To me it feels like the way SQL server security was meant to be done.
Sure there can be some downsides. You can’t just put table names in your queries. You always have to prefix your tables and objects with the correct schema name. Of course, it can be argued that you should be doing that anyway, even if your schema is dbo. You also need to be careful to decorate your stored procs, functions etc. with the correct custom schema when creating them.
Another issue is if you don’t name your schemas well, it can cause some confusion. I have worked at companies where a custom schema name matched an existing database name. It was very annoying. I would not suggest doing that. Also, it can be tempting to use an acronym for short name, but in my experience it is more helpful to have a custom schema name that is readable and meaningful to others. For example, it probably makes more sense to have PriceListObj. instead of PLO.
Steve Jones recently wrote an article on the importance of properly setting the owner of a custom schema. I think he makes some good points that are worth reading. If you missed it, you can see his article here: Being Careful with Schema Ownership
In the end, because of the organizational and potential security benefits I think custom schemas are a great resource for DBA and database developers. Share your experience with custom schemas. If you don’t use them explain why not?
Ben Kubicek from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums
