Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: db_datareader for new tables

    I have added someone to the db_datareader role and that is fine. However, if I add a new table to the database, they do not have 'Select' permission for the new table. Is this by design? Is there a way around this to give users read permission on all tables current and future? Thanks for any answers to this question.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I think the role db_datareader is a db-wide role, by default it applies to all tables/views in the database, including the newly created ones. You do not need to set the 'select'-permission.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A bit of a longshot, but run
    Code:
    sp_helprotect [table name]
    and see if someone has denied access to the table for some reason.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    According to Microsoft a member of db_datareader can read every table, past, present, and future.

    -PatP

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Explicity denying access to a table will trump db_datareader, but I don't think anything else does.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    in an unrelated note
    how about creating views for all those users.
    hmmmmmm?

    views have many advantages over direct table access.
    • they add a security layer between the user and the table
    • they mask database complexity
    • they can increase read performance
    • and they can give you a layer between the root object and the user so object name changes can occur without recompillation of the application.


    just a thought.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I know what you mean, Curt. The db_datareader role makes it awful hard to add things like a salary table to your database, too ;-).

    But then, when was the last time someone actually thought about security, anyway. I mean without the DBA storming over to his cube?

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i wont give a database to a developer until i explain the importance of views and stored procedures.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •