What are schemas for, exactly?

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?

more ▼

asked Feb 09, 2010 at 05:58 PM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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.

more ▼

answered Feb 09, 2010 at 08:24 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 09, 2010 at 05:58 PM

Seen: 1538 times

Last Updated: Feb 09, 2010 at 05:58 PM