locked
what is the different between Schema Name and Schema Owner? RRS feed

  • Question

  •  

    what is the different between Schema Name and Schema Owner?
    Tuesday, October 7, 2008 5:52 AM

Answers

  • Since SQL Server 2005 each of the objects belong to a schema. A database schema is a unique namespace that is separate from a database user. You can think of a schema as a container of objects.

    Schema name is the name of a schema. I am quoting this example from
    http://msdn.microsoft.com/en-us/library/ms190387(SQL.90).aspx
    where we create a schema named Auditing and assign the schema ownership to a user named Marjorie.
    CREATE LOGIN Marjorie
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';

    USE AdventureWorks;
    CREATE USER Marjorie FOR LOGIN Marjorie
    GO
    CREATE SCHEMA Auditing AUTHORIZATION Marjorie;
    GO
    dbo is the default schema you can change the schema owner to any valid user as shown above. The above link should help you understand better. Prior to Sql 2005 the database objects where owned by users. So if a user is quitting an organization and if he owns any database objects you need to change the individual object owners to another existing user before dropping the user who is quitting. This will be tedious if the user owns many objects. But with the introduction of schema, it is easy to assign the schema owner to another existing user.

    - Deepak
    Tuesday, October 7, 2008 6:12 AM