I know that I can put most objects in my database within a schema - but what is this for, and why would I want to? How do schemas relate to the users in my databases?
asked Feb 09, 2010 at 05:58 PM in Default
Matt Whitfield ♦♦
In previous versions of SQL Server, Schemas and Users were tightly intertwined. However, in SQL Server 2005 they have been largely decoupled and this makes them more useful.
There are a couple of good reasons to use schemas in your database. The first and most common is for security control. You can assign (or deny) permissions to a user on a schema. This means that you can conveniently assign permissions to an entire schema-grouped set of objects within the database instead of trying to do it object by object.
More subtly, they can help in logically grouping objects. This serves little technical purpose, but it can help hint to users/developers/yourself later-on how the objects are interrelated and interconnected which can be quite helpful.
There is a similar question (but not identical) question at http://ask.sqlservercentral.com/questions/310/differentiate-schema-and-ownership which is worth reading.
answered Feb 09, 2010 at 08:24 PM