SQL Server Schema

Schema : A schema is a collection of database objects (etc-tables) associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema.

Basically, any user who creates an object has just created his or her own schema.So, based on a user's privileges within the database, the user has control over objects that are created, manipulated, and deleted.

Suppose you have been issued a database username and password by the database administrator.

Your username is ted. Suppose you log on to the database and then create a table called EMPLOYEE_TBL. According to the database, your table's actual name is ted.EMPLOYEE_TBL. The schema name for that table is ted, which is also the owner of that table. You have just created the first table of a schema.

The good thing about schemas is that when you access a table that you own (in your own schema), you do not have to refer to the schema name. For instance, you could refer to your table as either one of the following:

EMPLOYEE_TBL 

ted.EMPLOYEE_TBL

 If another user were to query one of your tables, the user would have to specify the schema, as follows:

ted.EMPLOYEE_TBL

If Ted leaves the company or department and his account must be removed from the database, the ownership of the table must be transferred to another user account using the sp_changeobjectowner stored procedure before Ted's account can be removed.

Transferring Ownership of a Schema to a User:-

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO UserName;

Using Transact-SQL to create a schema :

USE AdventureWorks2012;

GO

-- Creates the schema Sprockets owned by Annik that contains table NineProngs. 

-- The statement grants SELECT to Mandar and denies SELECT to Prasanna.

    CREATE SCHEMA Sprockets AUTHORIZATION Annik

    CREATE TABLE NineProngs (source int, cost int, partnumber int)

    GRANT SELECT ON SCHEMA::Sprockets TO Mandar

    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;

GO

 How to know the list of system schemas and user defined schema names?

SELECT schema_id,name FROM sys.schemas

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

How to know the list of system schemas and user defined by schema ID?

SELECT SCHEMA_NAME(6);

How to rename the schema using query?

ALTER SCHEMA NewSchema TRANSFER OldSchema.Object;

Using SQL Server Management Studio to Create a Schema:-

1. In Object Explorer, expand the Databases folder.

  

2.Expand the database in which to create the new database schema.

   

3.Right-click the Security folder, point to New, and select Schema.

4.In the Schema – New dialog box, on the General page, enter a name for the new schema in the Schema name box.

  

5.In the Schema Owner box, enter the name of a database user or role to own the schema. Alternately, click Search to open the Search Roles and Users dialog box.

6. Click Ok

Additional Options:

The Schema– New dialog box also offers options on two additional pages: Permissions and Extended Properties.

• The Permissions page lists all possible Securables and the permissions on those Securables that can be granted to the login.
• The Extended properties page allows you to add custom properties to database users.