Should sql server schemas be used for logical or administrative grouping?

Question

Is it agood idea to divide my SQL Server objects into schemas based on their logical grouping (a kind of namespace s.a. sales, user, common) or should they be primarily used to create different security boundries?

Answer 1

i'd go for logical grouping and set security via roles. but usually the logical and security groups are the same. usually :)

Answer 2

You can put stored procedures in schemas based on security level. Then grant execute on the schema to a role. No more granting.

Answer 3

The new Microsoft project called "Oslo" uses schemas to group objects into namespaces.

Answer 4

I'd only perhaps separate based on user objects vs app/system objects. That is, orders, products etc are user objects. Security tables are system objects.

At some point too, you may find you have an overlap of permissions, if you grant rights to the schema (as suggested). Or you may not be able to classify an object, or it's used in different areas.

If you've been following best practice since well before SQL 2005 and upgraded through, you may have hundreds of databases where every object reference is schema qualified with "dbo". Do you diverge your naming conventions for new databases, and possibly break all your useful scripts that rely on "dbo."?

Edit: If you do decide to do this, beware of ownership chaining