none
What is the difference between db_accessadmin ROLE and db_accessadmin SCHEMA

    Question

  • The question is pretty simple so I don't see why it is so difficult to understand. I do hope somebody answers with something useful.

    The problem is that SQL Server 2005 defines several default ROLES such as db_accessadmin, db_backupoperator, db_datareader, etc. and at the same time it also defines SCHEMAS with the same name (db_accessadmin, db_backupoperator, db_datareader).

    I have no idea what MS developers where thinking but that is utterly confusing. When creating a role using Management studio one should be able to "grant" these default ROLES to the role one is creating (or the user), instead one is presented with schemas.

    For example, when creating a new database role if you select the db_datareader SCHEMA (because ROLES are not presented...) then you can select the various permissions (CONTROL, SELECT, ALTER, etc.). That is ok if the object is a user defined schema but totally confusing for default db_ schemas. Why would one grant other than SELECT to db_datareader schema?

    I am still confused about this and is hindering the implementation of a proper database role structure.

    Friday, May 16, 2008 9:34 AM

Answers

  • For a detailed description on user and schema separation in SQL Server 2005, please refer to BOL: User-Schema separation.


    I can see how SSMS dialog can be confusing, I strongly encourage you to report this usability problem in SSMS using Microsoft Connect.


    Please let us know if you have any additional questions in SQL Server security please let us know; if you have further questions regarding tools such as SSMS, I would really encourage you to use the SQL Server Tools General forum.


    Thanks,

    -Raul Garcia

    SDE/T

    SQL Server Engine

    Monday, May 19, 2008 5:59 AM
    Moderator
  •   I misunderstood your question; sorry about that, I thought it was related to SSMS.

     

      These schemas are simply artifacts of the user-schema separation, and they exist simply for backwards  compatibility reasons, they serve no other purpose, and I would like to discourage anyone from using them as well.

     

      I hope I was able to answer your question.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Monday, May 19, 2008 9:52 PM
    Moderator

All replies

  • For a detailed description on user and schema separation in SQL Server 2005, please refer to BOL: User-Schema separation.


    I can see how SSMS dialog can be confusing, I strongly encourage you to report this usability problem in SSMS using Microsoft Connect.


    Please let us know if you have any additional questions in SQL Server security please let us know; if you have further questions regarding tools such as SSMS, I would really encourage you to use the SQL Server Tools General forum.


    Thanks,

    -Raul Garcia

    SDE/T

    SQL Server Engine

    Monday, May 19, 2008 5:59 AM
    Moderator
  • Yes I have read that User Schema document many times, I know what a user, a schema, a role, etc. is. My question was about what was the difference between both things with the same name outside the obvious (that one is a schema and the other a role and the purpose of each of them).

     

    Really, I don't see what is the purpose of a db_datareader schema, a db_datareader role yes that is very clear.

     

    I see the same general confusing nomenclature in the ASP.NET membership system where there are a bunch of roles and schemas with the same name.

    Monday, May 19, 2008 9:53 AM
  •   I misunderstood your question; sorry about that, I thought it was related to SSMS.

     

      These schemas are simply artifacts of the user-schema separation, and they exist simply for backwards  compatibility reasons, they serve no other purpose, and I would like to discourage anyone from using them as well.

     

      I hope I was able to answer your question.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Monday, May 19, 2008 9:52 PM
    Moderator