What is purpose of database Schema?

Ø  What is Schema ?

      A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
                                      (OR)
We can simply telling , Schema is nothing but Collection of  Database Objects under a database pricipal.

You can assign a user login permissions to a single schema, so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Ø  Advantages of Database Schema?
     * To group the similar tables
     * To Grant permissions on more than one table at a time
     * No need to change the owner of table(s) if the user is deleted.
     * Objects are not attached to any specific user account. So if the user account needs to be deleted we don’t have to worry about changing the objects owners.
     * It simplifies managing permissions on Schema objects. If the schema owner’s account is to be removed from the database, the ownership of the schema can be transferred to other user without breaking any code.
     * Use of Schema’s with database roles can simplify managing security. I have tried to explain it in more detail later in this post.
     * Overall, maintenance of database become easier and I will recommend the use of schemas if you’re working with more than 20 tables.
Ø  How to create Schema?
    When you create any objects in SQL Server 2005/2008/2008R2/2012/2014 they are allocated to a default schema which is “dbo” (database owner account) in most of the cases. However it is recommended to explicitly specify the schema name when creating objects. 
Syntax:To create Schema
            Create Schema <SchemaName>
Eg: 
Step-1: Use RDYadav
                Go
                Create Schema Library
Step-2: Create table Library.Books(Bid int,Bname Varchar(40))
                Create Table Library.Students(StudId int,Sname varchar(40) ) 
Ø  How to Transfer one schema into another Schema?   
Syntax: ALTER SCHEMA newschema TRANSFER oldschema.TABLEName
Eg: ALTER SCHEMA Library TRANSFER dbo.Pencils

Syntax:  To Grant schema level permissions
Grant ....on schema::[schemaName] to <username>/<rolename>

Eg: Granting permission on Library schema to Madhuri Dixit
       Use RDYadav
       Go
       GRANT SELECT on Schema::[Library] to Madhuri Dixit
       
DENY INSERT ON SCHEMA::Library TO Madhuri Dixit      


Ø  How to know the what permission having a particular user ?

      SELECT state_desc, permission_name, 'ON', class_desc,SCHEMA_NAME(major_id),'TO',USER_NAME(grantee_principal_id)  FROM sys.database_permissions AS Perm  JOIN sys.database_principals AS Prin  ON Perm.major_ID = Prin.principal_id AND class_desc = 'SCHEMA' WHERE major_id = SCHEMA_ID('Library')    AND grantee_principal_id = user_id('TestUser')

      --AND    permission_name = 'SELECT'