Simplifying a Database Role SQL 2005 RRS feed

  • Question

  • hello, I have created a Database Role and want to allow this role to have "SELECT" access to all Tables and Views in my DB. From what I am seeing I have to select each table and view in the Securables section, then click the Select Permission in the Explicit Permissions section. There must be a faster way to accomplish this task.

    Its being created for users that will access the DB using Crystal Reports.

    Any help would be greatly appreciated.
    Thursday, June 25, 2009 2:44 PM

All replies

  • Hi Triley

    You can add your new role to the db_datareader role. This is a built-in database role that grants exactly the privileges you need.



    If you have found this post helpful, please click the 'Vote as Helpful' link under the star. If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Proposed as answer by NimitPParikh Thursday, June 25, 2009 5:19 PM
    Thursday, June 25, 2009 3:27 PM
  • An optional way of doing this is to grant SELECT permissions on the database.

    GRANT SELECT ON DATABASE::<database name> TO <database principal>

    I prefer this method, because it is a LOT more flexible than the built-in database roles that ship with SQL Server.  While db_datareader and granting SELECT on the database really have no differences.  You start to have issues when you want to do something like granting INSERT and UPDATE on all tables without granting DELETE.  By using the built-in roles, you have to add the account to db_datareader + db_datawriter (you can't modify if you can't read the data) and then go back and issue a deny on every table/view in the database while also remembering to maintain this going forward.  Instead you of using the built-in roles, you can just issue:

    GRANT SELECT, INSERT, UPDATE ON DATABASE::<database name> TO <database principal>

    Same type of thing if you want to give an account permission to execute every stored procedure:

    GRANT EXECUTE ON DATABASE::<database name> TO <database principal>

    BTW, you can also do this at a schema level:

    GRANT <permission> ON SCHEMA::<schema name> TO <database principal>

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Friday, June 26, 2009 2:54 AM