var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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.
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.
A bit of a longshot, but run
and see if someone has denied access to the table for some reason.
sp_helprotect [table name]
Microsoft a member of db_datareader can read every table, past, present, and future.
Explicity denying access to a table will trump db_datareader, but I don't think anything else does.
in an unrelated note
how about creating views for all those users.
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.
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?
i wont give a database to a developer until i explain the importance of views and stored procedures.