locked
GRANT SELECT or db_datareader for custom DB role RRS feed

  • Question

  • Hi all

    I have been playing about with a custom Database Role on my 2008R2 SQL Server and came across a question which I thought i'd put to the masses....

    My role has an EXECUTE schema securable against it for SP execution but I also would like the role to have readonly access to all tables in the database.  I initially thought of assigning the custom DB role with the db_datareader role using sp_addrolemember but i've recently discovered that you can GRANT SELECT on the database tables within the custom role without using the db_datareader fixed role.  What is the difference between the 2 methods explained?  Are there any negatives in in GRANT SELECT rather than applying db_datareader, security or otherwise?

    Many thanks.

    • Changed type Naomi N Monday, December 3, 2012 12:50 PM Question rather than discussion
    • Moved by Tom Phillips Monday, December 3, 2012 8:47 PM Security question (From:Transact-SQL)
    Monday, December 3, 2012 12:26 PM

Answers

  • The fixed database roles, such as db_datareader, have been around for decades. They are still fully supported, but these roles are clumsy sledge hammers. The newer permission system (first included in SQL Server 2005) is much more flexible and precise. As you can see in the second graphic on this page http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx the db_datareader fixed database role is equivalent to GRANT SELECT ON DATABASE::<some_database_name>

    For a complex enterprise database system, I would recommend using the new system. All 214 permissions are shown in the Permissions Poster If you are working on a less complex departmental database, and if a role, such as db_datareader, exactly meets your needs, go ahead and use it. It's less work, but less flexible.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Monday, December 10, 2012 5:03 PM
    Monday, December 3, 2012 10:01 PM

All replies

  • Hi divvyboy,

    db_datareader applies to all tables in the database, whereas you can apply GRANT SELECT to a specific table or set of tables.  

    I have one project where sensitive financial information is stored in one of the tables, with non-sensitive information in others.  The software that interacts with the database does not support stored procedures, it requires direct access to the tables.  In this case, we granted that user account SELECT on the non-sensitive tables, while still blocking it from the sensitive data.



    -- Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, December 3, 2012 12:31 PM
  • The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database

    GRANT SELECT you assign to the single object or on schema.

    But if you have custom role(GRANT SELECT) I think it would better for specific application rather then adding users to db_datareader database fixed role..


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, December 3, 2012 12:39 PM
  • My role has an EXECUTE schema securable against it for SP execution but I also would like the role to have readonly access to all tables in the database.  I initially thought of assigning the custom DB role with the db_datareader role using sp_addrolemember but i've recently discovered that you can GRANT SELECT on the database tables within the custom role without using the db_datareader fixed role.  What is the difference between the 2 methods explained?  Are there any negatives in in GRANT SELECT rather than applying db_datareader, security or otherwise?

    The difference is that db_datareader fixed database role applies to all objects in the database whereas your custom role can be more granular, such as to a specific schema like your case.  The only negative that comes to mind is the additional administrative task of creating the custom role and granting the desired permissions.  Personally, I prefer custom roles because of flexibility.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, December 3, 2012 12:44 PM
  • The users are sourced from Active Directory and then applied to the custom role.  I think i'll go with the GRANT SELECT on the database rather than applying a fixed role on a custom role - makes more sense to me and to second what Dan has pointed out, it's more flexible.

    Thank you all for your advice and guidance.

    Monday, December 3, 2012 1:38 PM
  • Hi ,

    Do custom role as you required :"Creating User-Defined SQL Server Database Roles"

    Click on link below how to do :

    http://msdn.microsoft.com/en-us/library/aa905188(v=sql.80).aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, December 3, 2012 2:49 PM
  • The fixed database roles, such as db_datareader, have been around for decades. They are still fully supported, but these roles are clumsy sledge hammers. The newer permission system (first included in SQL Server 2005) is much more flexible and precise. As you can see in the second graphic on this page http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx the db_datareader fixed database role is equivalent to GRANT SELECT ON DATABASE::<some_database_name>

    For a complex enterprise database system, I would recommend using the new system. All 214 permissions are shown in the Permissions Poster If you are working on a less complex departmental database, and if a role, such as db_datareader, exactly meets your needs, go ahead and use it. It's less work, but less flexible.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Monday, December 10, 2012 5:03 PM
    Monday, December 3, 2012 10:01 PM