SQL SERVER – Importance of Database Schemas in SQL Server

Beginning with SQL Server 2005, Microsoft introduced the concept of database schemas. A schema is now an independent entity- a container of objects distinct from the user who created those objects. Previously, the terms ‘user’ and ‘database object owner’ meant one and the same thing, but now the two are separate.

This concept of separation of ‘user’ and ‘object owner’ may be a bit puzzling the first time one encounters it. Perhaps an example may better illustrate the concept: In SQL Server 2000, a schema was owned by, and was inextricably linked to, only one database principal (a principal is any entity or object that has access to SQL Server resources, for example a user, role or a group). This meant that if, say, a user creates a table in the database, that user cannot be deleted without deleting the table or first transferring it to another user. But in SQL Server 2005 one can now simply create the table first and attach it to a schema, even without having created the user. This can be accomplished via the Transact-SQL statement below:

CREATE TABLE MySchema.MyTable (col1 int, col2 int)

Note that here ‘MySchema’ refers to the schema that owns ‘MyTable’, as contrasted to SQL Server 2000 in which for the same statement, ‘MySchema’ would have referred to the user who owns the table. This separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.

Default Schema

Because objects are no longer tied to the user creating them, users can now be defined with a default schema. The default schema is the first schema that is searched when resolving unqualified object names.

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. It is important note that if the user is authenticated by SQL Server via the Windows operating system, no default schema will be associated with the user. Therefore if the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema, though not directly with the user.

Implications

The separation of ownership from schemas has important implications:

  • Ownership of schemas and schema-owned objects is transferable. This is accomplished using the ALTER AUTHORIZATION command.
  • Objects can be moved between schemas. This is accomplished using the ALTER SCHEMA command.
  • A single schema can contain objects owned by multiple database users.
  • Multiple database users can share a single default schema.
  • Permissions on schemas and schema-contained objects can be managed with greater precision than in earlier releases. This is accomplished using schema GRANT permissions object GRANT permissions.
  • A schema can be owned by any database principal. This includes roles and application roles.
  • A database user can be dropped without dropping objects in a corresponding schema.
  • Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
  • Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.
  • Object access and manipulation are now more complex as well as more secure since they involve an additional layer of security.

Advantages of using schemas

Apart from the obvious benefit that objects can now be manipulated independently of users, usage of schemas also offers the following advantages:

Managing logical entities in one physical database: 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. Schemas help to manage the logical entities separately from one another, but still allow objects to work together where required.

Object protection: Through schemas, a DBA can control access to crucial objects that would otherwise be open to potentially destructive changes by the users.

Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself. Using schemas, a developer can logically group objects and even create custom objects without running the risk of messing up the underlying ISV database.

Conclusion

Schemas, introduced in SQL Server 2005, offer a convenient way to separate database users from database object owners. They give DBA’s the ability to protect sensitive objects in the database, and also to group logical entities together.

Reference : Pinal Dave (http://blog.sqlauthority.com),

http://msdn.microsoft.com/en-us/library/dd283095.aspx, http://msdn.microsoft.com/en-us/library/ms190387.aspx

About these ads

32 thoughts on “SQL SERVER – Importance of Database Schemas in SQL Server

  1. Dear Pinal,

    Greetings to you. I would like to maintain two database. one for storing the data from the GUI and another database for reporting service(SSRS).

    Now i want to move the data from orginal database to reporting database. how can i handle, either through trigger or any other method.

    Need advice.

    Thanks in advance.

    Stanley Christopher R

    • Though my answer is too late, but might be helpful for some other reader.
      Best way is to implement either Log Shipping to other Server, or go for database mirroring.
      This way you will have another server from which you could easily generate reports. There will be some lag time for log shipping, but that can be manageable.

      Regards,
      Anjum Niaz

  2. hi pinal,
    I have to create a primary key in the following format
    ERD001,the value of numerical part will increase on subsequent inserts,like ERD002,ERD003,ERD004

    • First Create the table like as:

      Create table Details1
      (p_no varchar(7) default ‘ACE0000′,
      name varchar(15)
      )

      Then we will write a one trigger:

      like as:-

      Alter trigger insteadofInsert on Details1
      Instead of insert
      As
      Declare @p_no varchar(4);
      Declare @char varchar(7);
      Declare @Name varchar(15);
      set @p_no= (select right(’000′+max(cast(substring(p_no,4,4)as int)),4) from Details1)

      select @char=substring(i.p_no,1,3) from inserted i;

      select @Name=i.Name from inserted i;
      set @p_no=cast(’000′+@p_no+1 as varchar);

      begin
      insert into Details1 values (@char+(select right(’000′+CAST(@p_no as varchar),4) as Changedformat),@Name)

      end

      Testing:

      Then you insert the values into a table like as:-

      insert into Details1 values(default,’Kabilan’)

      By

      Kabilan K…..
      [email and phone removed due to privacy reasons]

  3. Hi Pinal,

    I have created 2 schemas in one database and trying to run the script in order to create tables under schemas. but problem is that script has another schema inside it and when i run that script its giving me error like:

    User does not have permission to perform this action.
    Msg 2759, Level 16, State 0, Line 2
    CREATE SCHEMA failed due to previous errors.”

    E.g

    I have 2 schemas say Tarun1,Tarun2 under abc database. now I want to add some table under Tarun1, Tarun2 schema.

    Here is script:
    – Create schema for common tables
    create schema CM_FRAMEWORK_TABLES
    ;

    – Create common tables
    create table CM_FRAMEWORK_TABLES.Table1(
    ID nvarchar(20) not null,
    TYPE nvarchar(50) not null,
    SCRIPT varbinary(max),
    primary key(ID, TYPE)
    )
    ;

    Can we add CM_Framework_Tables schema under Tarun1 schema? If yes then please suggest me how we can do that? what Kind of permissions we need?

    Waiting for your reply!
    Thanks in advance

    Tarun

  4. Dear Pinal,

    One may encounter many people with a great deal of knowledge and experience. Yet it is a great pleasure reading vast knowledge that is clearly and simply put – especialy when the material in itself is not necessarily simple.
    After reading some of your papers here I cannt escape Albert Einstein’s strong opinion the importance of simplicity.

    Thnaks and all the best,

    Tzally

  5. Dear Pinal,

    Please brief me the use of casting any variable to varbinary for comparison of varchar type data

    WHere cast(@Username as varbinary(20))=cast(Username as varbinary(20))

  6. If there are two schema in one database, is there an impact on the database performance? If yes, please provide more details. Thank you.

  7. @Jamie

    According to me there should not be any performance issue if you create multiple Schema’s considering.

    SQL Experts can give more details.

    ~ IM.

  8. Mr. Pinal,

    Here is my scenario: I transferred a database with two different schema namely dbo, and hilmarc to a new server. Both server is SQL Server 2005, the problem is every time i try to run my query with schema name hilmarc on the new server i get the error message “Invalid object name” but when i put the name of the schema hilmarc before the name of the table it’s working.

    What i want to know is how can i execute my query without indicating the schema name. I have a lots of query to change if i need to include the schema.

    Please help. Thanks!

  9. Hi, pinal,

    what exactly the difference between schema and role , i need to read permission to the user when i opened a windows in ssms it is showing schemas – datareader and role – datareader what should i select, i understand about the schema what u said as above but little bit confusing about schemas and roles.

    Thanks pinal

  10. I am working on multiple schema’s. these are the sample query’s from different schema

    SELECT [DepartmentID],[DepartmentName],[Description]
    FROM [DemoDB].[dbo].[Department]

    SELECT [EmpID],[EmpName],[DepartmentID]
    FROM [DemoDB].[BOA].[Employee]

    SELECT [EmpID],[EmpName],[DepartmentID]
    FROM [DemoDB].[BHW].[Employee]

    All table which is created in dbo schema are default and master tables. The following SP is created in dbo schema

    SP
    ===================
    ALTER PROCEDURE [dbo].[GetEmployeeDetails]
    @DeptID int
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT E.EmpID, E.EmpName, D.DepartmentName FROM Employee E
    INNER JOIN dbo.Department D ON D.DepartmentID = E.DepartmentID
    WHERE E.DepartmentID = @DeptID
    END
    ===================

    when i try to run the SP from different users [BOA/ BHW] my SP is throwing error “Invalid object name ‘Employee’”.

    if the run the query in NEW QUERY window with specified user logged in then the query is executing properly. but my SP is not working.

    I want to run the SP to retrieve the data from table who ever logged in to the database. when ever i create any user it will copy all the default table to the newly created user by creating new schema with the new username. my all master table will be in dbo schema and other tables will be copied to each schema, like

    BOA.Employee [Linked to master table]
    BHW.Employee [Linked to master table]
    dbo.Department [Master Table]

    i want the solution to the SP which are created in dbo, it should get the data specific to the user logged in to the DB. If i logged in with BOA then it should retrieve the data from BOA tables..

    • Here MySchema2 should be the schema to which you want to move the table MyTable and MySchema1 is the schema which currently contains the table MyTable.
      Am I right Pinal Sir ?

  11. My personal opinions is SQL takes more server load than mysql.
    First I learned mysql then I am on SQL server.
    Well main IMP advantage of SQL server is you can use it in VB or ASP or in ay other things.

  12. Hi Pinal,
    I ma new in Datawarehousing, ca u suggest me any books related to SQL datawarehousing for ETL like SSIS, SSRS etc.
    I am having more than 8 yrs. exp in Microsoft tech.

  13. How to remove schema from MS SQL SERVER 2005.

    script to remove all the table belongs to particular schema .?

    Please advice..

  14. @joji,
    DROP SCHEMA schema_name
    Note: first drop all tables using that schema
    or, change the schema if you don’t want to drop the table.
    syntax -
    ALTER SCHEMA another_schema_name TRANSFER schema_name.table_name

    Pinal Sir,
    Please correct me if I am wrong somewhere.

  15. Very helpful article. I only half understood the schema concept till now. This is one of the clearest articles on the topic I have read.

  16. Pinal,

    I am a frequent visitor to your site, and just wanted to say thanks for the info. You’re always “right on the money” with advice and instruction.

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  18. Hi, How many Schema we can create in a single instance of sql server 2012, and is it will be good practice to create separate schema for every customer if i have number of….

  19. Like your article, very informative. I would however have like some discussion of how to deal with the two situations you mentioned in the article…

    1. Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
    2. Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.

    When working in enterprise situations many times you have newer SQL Servers accessing older databases located elsewhere, and you only have “old code” or “old constructs” that have been migrated forward without regard to the issues mentioned above and they end up no longer working.

    A discussion detailing the old way of referring to users/schemas, that were essentially the same, with examples how one now must code it in the new delineated schema approach to access those “old” constructs.

    As an example, I have a linked server on a machine running SQL Server 2008 R2, which is attempting to insert records into a table on an SQL Server 2000 machine. Both the source table and destination table are named the same and have the same structure, but when attempting to perform the insert, we get a message of: ‘Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLT10″ for linked server {xxx}”…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s