Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database. I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level.

But that got me thinking, what is the difference (if any) between granting a user SELECT permission at a database level, or adding them to the db_datareader role? And is there any best practice one way or the other?

share|improve this question
    
The 2005 documentation says db_datareader also grants SELECT on views. The 2008+ documentation says nothing about that. I haven't tested to see which is correct, but that seems to be the only difference. –  Jon Seigel Aug 28 '13 at 15:36
    
Hello! For me, I would use db_datareader in a situation where I knew I wanted a user to have blanket select abilities against the whole database. Where I knew users needed more granular access, I would grant connect to the database then select against the objects or schemes as appropriate. It's also worth bearing in mind that when you grant db_datareader any Denys further on will trump it. –  DBAWaffle Aug 28 '13 at 20:07
    
So you would use db_datareader over granting SELECT on the database? Any particular reason? –  Kenneth Fisher Aug 28 '13 at 20:20
    
@kennethFisher I grant selects over db_datareader when the user in question doesn't require select access against the whole database but only against certain objects. –  DBAWaffle Aug 28 '13 at 20:43
    
I have a vague memory that db_datareader did not grant permission to table-valued functions in an older version (2000? 2005?). I can't find any definite documentation, however, and I don't have older versions to test. I can say that in SQL Server 2012, membership in db_datareader does grant select permission to inline table-valued functions. –  Greenstone Walker Aug 29 '13 at 0:00
add comment

1 Answer

up vote 2 down vote accepted

Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx

The chart shows that db_datareader role is identical to GRANT SELECT ON [database]. So it is still fine to use, but the recommendation is to move away from those roles to the more granular commands. Some of the other fixed database roles are less clearly defined for most people. Using the explicit commands results in greater clarity when reporting rights.

Obviously you know how to grant finer grained permissions. I am trying to break loose from the old roles whenever possible, but db_owner (for example) is a hard habit to break.

share|improve this answer
    
Excellent link! I'm going to have to share that one around. –  Kenneth Fisher Aug 28 '13 at 15:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.