none
SQL Server Basics

    Question

  • Hi,

    I am new to sql server, thought I have extensive experience (15 years) in all other major RDBMS's (Oracle, MySQL, Informix,...). But now I have to work with SQL server with which I have no experience. I am trying to learn the basics of sql server, I have the following questions:

    1> What is the difference between a database user and a database schema? which of these is the owner of a table? I mean, when we refer to a table using it's fully qualified name, is the table name prefixed with the schema name or the name of user who created it?

    2> Can a schema contain tables created by different users of the database?

    3> When we use grant and revoke (for giving or denying permissions) statements, is  the grantee a user or schema? that is, are permissions granted/revoked to/from  users or schemas?

    Can someone please reply to these questions? I will be highly thankful.

    Amir.

     

     

    Friday, December 03, 2010 3:34 AM

Answers

  • Hi Amir,

    The relation between user and schema is that a user (or role) owns the schema. The owner doesn't have to be the creator, as ownership to a schema can be delegated.

    I made an incorrect statement in my previous post, actually owning a schema and has it as default schema is not the same. For example sysadmin can own a schema and set it as default schema for a user.

    "Does it mean that tables created by different users are owned by those users, but reside in the schema just for the purpose of grouping... that is, the owner of a table is always a user (who created it), and a schema is just a container of tables, just to group related tables. Is all this correct?" Yes, it is almost correct, except that in Sql Server you can add owners to a table, so a table can have several owners.

    For further reference please start with the following links:
    User-Schema Separation

    CREATE SCHEMA (Transact-SQL)

    Good luck, I will be happy to answer more questions, if I can.

    Regards

    Istvan

    Friday, December 03, 2010 5:23 PM
  • Does it mean that tables created by different users are owned by those users, but reside in the schema just for the purpose of grouping... that is, the owner of a table is always a user (who created it), and a schema is just a container of tables, just to group related tables. Is all this correct? correct me if any of these statement is wrong.

    In SQL 2000 tables were owned by the user that created them, unless you specified otherwise (or were connected as a sysadmin user) and set the owner to dbo. Of course the problem with this could be that if the user left, you had database objects owned by a user who no longer existed.

    From SQL 2005 onwards tables (and other db objects) are "owned" by the schema rather than a specific user. In fact if you compare the syntax for CREATE TABLE in 2000, 2005 and 2008 you'll see there is no option to specify an "owner" in 2005 and 2008. Perhaps a more accurate way of thinking about it is that rather than a table being owned by a schema, it is assigned to one. One way to think of them is a way to create a layer of separation between user and object, so you never have to specify anything in code etc that relates to a user, which of course may change in time. In theory for large DB's they can be useful for database level security as mentioned before, though in reality I haven't heard of many people actually using them to that extent.

    Saturday, December 04, 2010 5:24 PM

All replies

  • Hi Amir,

     

    Consider you have a single database for the entire team thru some application.  So as a DBA what we done, we craete a multiple user in database level and we make a different schema like

    HR,

    Finance,

    Recourse,

    Marketing, etc.,

     

    Now as per your scenario. If you want to create table that should be used only by finance dep then you make a table under the schema finance.tablename. So the user which are belongs to the finance can able to be access, other user can't have the access the finance  table.

     

    Note:- Schema's are mainly used for the database level security system

    .


    Thiru - MS SQL World Regards, RM Thirunavukkarasu MCP, MCITP, ITIL V3 | "http://thiruna.blog.com" |Click "Propose As Answer" if solution is helpful to you.
    Friday, December 03, 2010 3:55 AM
  • Hi Ramasamy,

    Thanks for your reply, but I am still not very clear. Iam confused about users, schemas, tables...., who owns what? who permissions are given to? and can multiple users share objects of a single schema?

    Can any one else please see my questions given in my last post, and respond to them?.

    Thanks,

    Amir.

    Friday, December 03, 2010 4:38 AM
  • If you want to create the user by default in dbo schema. When at the time of creating user select the schema what ever you create it before like finance, hr ect,. Then the user is created under the schema of the particular group.

     

    Multiple user can able to use the single schema.

    Note:- Schema is like same as group policy in windows level

    Note:- Schema is owned by user only. Under the schema you can able to create the different user, tables ect.,


    Thiru - MS SQL World Regards, RM Thirunavukkarasu MCP, MCITP, ITIL V3 | "http://thiruna.blog.com" |Click "Propose As Answer" if solution is helpful to you.
    Friday, December 03, 2010 5:22 AM
  • Hi Rmasamy,

    I am confused by your conflicting statements....

    at one place you said "Then the user is created under the schema of the particular group",

    at second place you said "Schema is owned by user only",

    then at 3rd place you said "Under the schema you can able to create the different user",

    From above statements, the first and third mean; that users are created under schemas, meaning schemas own users, but your second statement means that Schema is owned by user,   which is exactly the opposite of the other 2 statements....

    Can you exaplain ?

    Thanks.

    Friday, December 03, 2010 6:11 AM
  • Hi,

    Consider I'm the manager for the entire team. So i can able to create schema and owned by myself. Under the schema i can able to create my team member depend up on the departments. Once i think that i don't need Marketing schema. Then who delete this Schema or modify the properties?. That's why we need a ownership for each and every schema for modification & deletion.


    Thiru - MS SQL World Regards, RM Thirunavukkarasu MCP, MCITP, ITIL V3 | "http://thiruna.blog.com" |Click "Propose As Answer" if solution is helpful to you.
    Friday, December 03, 2010 6:23 AM
  • Hi Amir,

    I resume that we are talking about Sql Server 2005 and upward, as this is different from version 2005.

    1. A schema is like a container for objects. As pointed out, it is used for security and to group database objects. You give permissions to users or roles. Roles are not really needed, but handy if several users should have similar permissions. There are some default roles already defined in SQL Server. If you refer to a table with fully qulified name like <database>.<schema>.<table>, you get access to that table, if you have the permission on the table/schema and can do operations (select, update, ...) according to your permissions. A user can have a default schema, meaning the user owns the schema. In that case, if you don't use fully qualified name, you get objects first from your schema, then from dbo, which is a special schema for compatibility reasons.

    2. Yes a schema can contain tables created by different users.

    3. Grant and revoke we give them to a user (or role).

    One additional complication, that you already might be aware of, is login vs user. You define login on the server/instance level and user on the database level.

    I hope this was to some help.

    Regards
    Istvan Kathi

    Friday, December 03, 2010 9:54 AM
  • Hi Kathi,

    Thank you so much.... your answer is really helpfull, and it removed almost all of my confusion. I need a few more clarifications:

    > 2. Yes a schema can contain tables created by different users.

    Does it mean that tables created by different users are owned by those users, but reside in the schema just for the purpose of grouping... that is, the owner of a table is always a user (who created it), and a schema is just a container of tables, just to group related tables. Is all this correct? correct me if any of these statement is wrong.

    What is the relation between users and schema?, does a user own schema(s)?, or a schema owns user(s)? who is the owner of other?, and in case a user owns a schema, does that user owns all the objects inside that schema? (meaning it can do anything with schema's objects)

    Thanks in advance.

    Amir.

    Friday, December 03, 2010 4:20 PM
  • Hi Amir,

    The relation between user and schema is that a user (or role) owns the schema. The owner doesn't have to be the creator, as ownership to a schema can be delegated.

    I made an incorrect statement in my previous post, actually owning a schema and has it as default schema is not the same. For example sysadmin can own a schema and set it as default schema for a user.

    "Does it mean that tables created by different users are owned by those users, but reside in the schema just for the purpose of grouping... that is, the owner of a table is always a user (who created it), and a schema is just a container of tables, just to group related tables. Is all this correct?" Yes, it is almost correct, except that in Sql Server you can add owners to a table, so a table can have several owners.

    For further reference please start with the following links:
    User-Schema Separation

    CREATE SCHEMA (Transact-SQL)

    Good luck, I will be happy to answer more questions, if I can.

    Regards

    Istvan

    Friday, December 03, 2010 5:23 PM
  • Does it mean that tables created by different users are owned by those users, but reside in the schema just for the purpose of grouping... that is, the owner of a table is always a user (who created it), and a schema is just a container of tables, just to group related tables. Is all this correct? correct me if any of these statement is wrong.

    In SQL 2000 tables were owned by the user that created them, unless you specified otherwise (or were connected as a sysadmin user) and set the owner to dbo. Of course the problem with this could be that if the user left, you had database objects owned by a user who no longer existed.

    From SQL 2005 onwards tables (and other db objects) are "owned" by the schema rather than a specific user. In fact if you compare the syntax for CREATE TABLE in 2000, 2005 and 2008 you'll see there is no option to specify an "owner" in 2005 and 2008. Perhaps a more accurate way of thinking about it is that rather than a table being owned by a schema, it is assigned to one. One way to think of them is a way to create a layer of separation between user and object, so you never have to specify anything in code etc that relates to a user, which of course may change in time. In theory for large DB's they can be useful for database level security as mentioned before, though in reality I haven't heard of many people actually using them to that extent.

    Saturday, December 04, 2010 5:24 PM