Schema

A schema is a collection of database objects that are owned by a single principal and form a single namespace. Schemas own objects and principals own schemas. A schema can be owned by either a primary or secondary principal, with the term “principal” meaning any SQL Server entity that can access securable objects.

Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

            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.

Schemas provide the opportunity to simplify administration of security, backup and restore, and database management by allowing database objects, or entities, to be logically grouped together. This is especially advantageous in situations where those objects are often utilized as a unit by applications. For example, a hotel-management system may be broken down into the following logical entities or modules: Rooms, Bar/Restaurant, and Kitchen Supplies. These entities can be stored as three separate physical databases. Using schemas however, they can be combined as three logical entities in one physical database. This reduces the administrative complexity of managing three separate databases.

CREATE LOGIN [User1] WITH PASSWORD=N'User1'

CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Pract]

CREATE ROLE [Pract_Role] AUTHORIZATION [dbo]

CREATE SCHEMA [Pract_Sch] AUTHORIZATION [User1]

GRANT DELETE,SELECT,INSERT,UPDATE,ALTER ON SCHEMA::[Pract_Sch] TO [Pract_Role]

EXEC sp_addrolemember N'Pract_Role', N'User1'

GO

GRANT CREATE TABLE TO [Pract_Role]

1.       We have created a login called User1

2.       Created a user with same name

3.       Created a role

4.       Created a schema under User1

5.       Granted permissions on schema to the role

6.       Added the role to the user

7.       Granted create table to the role

Here, we can observer that we have to grant permissions on table/schema to the role.