locked
Plus\minues of Schemas RRS feed

  • Question

  • We have a number of developers that want to use sql 2008 schemas as a way to group table names together 

    What are the pluses and minuses of using (non-dbo) schemas in a database?

    TIA,

    Barkingdog
    • Moved by Tom PhillipsEditor Friday, March 5, 2010 1:43 PM Database Design Question (From:SQL Server Database Engine)
    Friday, March 5, 2010 12:09 AM

Answers

  • Hello,

    Pluses:
    1. Objects are contained in groups that make sense - i.e. HR, Shipping, etc. which makes it easier to differentiate roles.
    2. Permissions can be given on schemas and users added to those schemas, simplifying permissions.
    3. Allows for multiple revisions of an object for example a possible change to an existing object using live data in a different schema for testing.

    Minuses:
    1. Poorly coded queries and applications may error if they don't reference the schema.object name.
    2. Schema sprawl - making schemas and objects too granular ending up with 500 different schemas for no reason.
    3. Schema upkeep, including diagnosing why users are having issues with certain queries or permissions.
    4. Permissions chaining trouble depending on design.


    Hope this helps,
    Sean
    Friday, March 5, 2010 11:00 AM
  • If you want to use multiple schema only to group objects for better readability then all schema should have same owner otherwise you would have to handle permission issue that would rise because of broken ownership chaining.

    If the owner of different schema are different than your following suspect of performance issue is correct:
    If a stored proc, HR.myproc, calls a table dbo.mytable won't the use of schemas adversely impact access speed as security now needs to be verified on the table (unlike the case with OC when both (proc and table) were owned by dbo?)

    Regards,
    Subahsh Chandra
    Founder: http://SQLReality.com/blog/
    Sunday, March 7, 2010 2:27 PM
  • Subhash,

    Yes, that make sense to me. But I note that, by default Sql server includes schemas such as "db_datareader". The owner of that schema is, by default,  the role "db_datareader" -- not "dbo" -- and the owner of that role is "dbo".

    From a permissions perspective, does that approach break the ownership chain we want to keep?  (After all, "dbo" is not directly the owner of the db_datareader schema; the role "db_datareader" is). Or putting it differently, should the owner of the schema "db_datareader" actually be "dbo" and not the role db_datareader?


    Barkingdog
    Sunday, March 7, 2010 4:03 PM

All replies

  • Hello,

    Pluses:
    1. Objects are contained in groups that make sense - i.e. HR, Shipping, etc. which makes it easier to differentiate roles.
    2. Permissions can be given on schemas and users added to those schemas, simplifying permissions.
    3. Allows for multiple revisions of an object for example a possible change to an existing object using live data in a different schema for testing.

    Minuses:
    1. Poorly coded queries and applications may error if they don't reference the schema.object name.
    2. Schema sprawl - making schemas and objects too granular ending up with 500 different schemas for no reason.
    3. Schema upkeep, including diagnosing why users are having issues with certain queries or permissions.
    4. Permissions chaining trouble depending on design.


    Hope this helps,
    Sean
    Friday, March 5, 2010 11:00 AM
  • Can someone amplify on Sean's statement?

    >>> Permissions chaining trouble depending on design.


    TIA,

    Barkingdog
    Friday, March 5, 2010 4:50 PM
  • Friday, March 5, 2010 5:40 PM
  • So, if I understood that article, each schema should have its own owner, preferably not al "dbo".

    If a stored proc, HR.myproc, calls a table dbo.mytable won't the use of schemas adversely impact access speed as security now needs to be verified on the table (unlike the case with OC when both (proc and table) were owned by dbo?)

    Barkingdog
    Friday, March 5, 2010 5:57 PM
  • I think this is a matter of preference. I put all related TABLEs of one data model in the same schema. If the data model has parts, they get prefixes. I use other schema for in-development revisions of the same model, for user's to have full-rights somewhere, and for a "cache" or a remote database.

    It really matters of the mind set of the designer/user.
    Friday, March 5, 2010 6:02 PM
    Answerer
  • I agree with Brian on this as I use it the same way. It's really a preference of the designer/developer. With most of the ISV's I work with, only one has had any type of schema design.

    -Sean

    Friday, March 5, 2010 6:29 PM
  • If you want to use multiple schema only to group objects for better readability then all schema should have same owner otherwise you would have to handle permission issue that would rise because of broken ownership chaining.

    If the owner of different schema are different than your following suspect of performance issue is correct:
    If a stored proc, HR.myproc, calls a table dbo.mytable won't the use of schemas adversely impact access speed as security now needs to be verified on the table (unlike the case with OC when both (proc and table) were owned by dbo?)

    Regards,
    Subahsh Chandra
    Founder: http://SQLReality.com/blog/
    Sunday, March 7, 2010 2:27 PM
  • Subhash,

    Yes, that make sense to me. But I note that, by default Sql server includes schemas such as "db_datareader". The owner of that schema is, by default,  the role "db_datareader" -- not "dbo" -- and the owner of that role is "dbo".

    From a permissions perspective, does that approach break the ownership chain we want to keep?  (After all, "dbo" is not directly the owner of the db_datareader schema; the role "db_datareader" is). Or putting it differently, should the owner of the schema "db_datareader" actually be "dbo" and not the role db_datareader?


    Barkingdog
    Sunday, March 7, 2010 4:03 PM