What is the importance of schema in sql server?

Question

What is the importance of schema in sql server?

Where this schema help me? Is it important for security reasons?

Answer 1

They partition your database to make management easier. This is from MSDN:

A schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

Here's the page that came from: http://msdn.microsoft.com/en-us/library/ms190387.aspx

In relation to security it makes it simpler to assign permissions as you can grant someone access to a schema without exposing your entire database to them.

Answer 2

What a schema is changed with the release of SQL Server 2005 and later - I think of it as an additional security layer as well as a container of objects.

This is quite a good resource: http://msdn.microsoft.com/en-us/library/ms190387(SQL.90).aspx

Answer 3

Yes, the primary purpose of SQL schema was -is- to facilitate security management: define who [which principals] can access what [which database objects]. This was made particularly easier starting with SQL 2005 when the schema stopped being directly tied to the owner.

Another use of schema is to serve as a namespace, that is preventing name clashes between objects from different schemas.
The original use of this was to allow multiple [interactive, i.e. ad-hoc like] users of a given database to create their own tables or stored procedures (or other objects), without having to worry about the existence of similarly named objects possibly introduced by other users.
The Namespace-like nature of schema can also be put to use in a planned database setting, i.e. one when a single architect designs the database structure in a way which provides distinct type of access, and indeed different behaviors, for distinct user groups.

Answer 4

See if this helps: http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas