Database Schema

 

 

In this article, I am trying to express my view related to SQL Server Schema. Hope it will be interesting and informative.

 

Definition

 

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.

 

You can assign 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.

 

Why Schema is needed

 

Schema is a new addition in SQL Server 2005 and a bit confusing to many database developers. Before we look into it, let's first revisit SQL Server 2000. This is a fully qualified query name to access a table in SQL Server 2000:

Select * from [DBServer].[DBName].[ObjectOwner].[Table]

I assume that you are aware of the difficulties that will arise when you try to drop a user who owns database objects. Before dropping the user, you need to assign all the objects belonging to that user to another user by using sp_changeobjectowner. If you do not reassign the objects first, you will have to drop all the objects belonging to the user before you can drop the user.

In SQL Server 2000, objects are tightly linked to users. This means that two users can have objects with the same names, which can lead to confusion in the development environment.

SQL Server 2005 provides a solution for this issue: a method called a schema. Think of a schema as a container that has boundaries. This container holds objects. Instead of accessing the table by object owner, as you did in SQL Server 2000, you can access it by schema:

Select * from [DBServer].[DBName].[Schema].[Table]

 

Default Schema

 

When you create any objects in SQL Server 2005/2008 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. A fully qualified object name in SQL Server 2005 now includes four parts delimited with dots, like this:

[Server].[database].[schema].[database-Objects]

As the syntax show, only the fourth element (database-object) is mandatory.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands.

 

Advantages of Schema's over Object Owners

1.    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.

2.    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.

3.    Use of Schema's with database roles can simplify managing security. I have tried to explain it in more detail later in this post.

4.    Overall, maintenance of database become easier and I will recommend the use of schemas if you're working with more than 20 tables.

 

 

SQL statements related to Schema are mentioned bellow

CREATE SCHEMA PROD

GO

CREATE TABLE PROD.Article (ID INT, Name CHAR(32))

GO

CREATE SCHEMA DBJ

GO

ALTER SCHEMA DBJ TRANSFER PROD.Article;

GO

DROP SCHEMA PROD

GO

To see the schema information use this

SELECT   TABLE_SCHEMA,

         TABLE_NAME,

         COLUMN_NAME,

         DATA_TYPE

FROM     INFORMATION_SCHEMA.COLUMNS

WHERE    TABLE_SCHEMA = 'DBJ'

ORDER BY TABLE_NAME,

         COLUMN_NAME

Select statement with schema information

SELECT * FROM  DBJ.Article

 

GRANT and REVOKE permission

CREATE SCHEMA DBJ

 

GRANT SELECT ON SCHEMA :: DBJ TO guest;

GRANT INSERT ON SCHEMA :: DBJ TO guest;

GRANT EXECUTE ON SCHEMA :: DBJ TO guest;

GRANT DELETE ON SCHEMA :: DBJ TO guest;

GRANT UPDATE ON SCHEMA :: DBJ TO guest;

 

REVOKE SELECT ON SCHEMA :: DBJ TO guest;

REVOKE INSERT ON SCHEMA :: DBJ TO guest;

REVOKE EXECUTE ON SCHEMA :: DBJ TO guest;

REVOKE DELETE ON SCHEMA :: DBJ TO guest;

REVOKE UPDATE ON SCHEMA :: DBJ TO guest;

 

I think the information of this article is quite informative and thanking you to provide your valuable time on it.

 

 

Posted by: MR. JOYDEEP DAS