Post reply

How to create a role and add data_reader and execute permission on 2 databases

  • August 25, 2020 at 4:38 pm

    #3783409

    Hi

    Can someone tell me the easiest way to create a new role (report_user_role)  on 2 databases which gives it the data_reader permissions and execute permissions on 2 databases.

    I then want to create a new user report_user and assign that role to it so it can read all tables in the 2 databases and execute all stored procedures.

    thanks in advance

     

     

    • This topic was modified 3 years, 8 months ago by  caz100.

  • August 26, 2020 at 8:59 am

    #3783691

    Key words to look up are

    CREATE ROLE,

    CREATE ROLE <rolename>

    GRANT,

    GRANT <permission> ON <object> TO <role/user>

    ALTER ROLE

    ALTER ROLE <rolename> ADD MEMBER <user>

    For reading I wouldn't go and try and do anything cleaver and just grant db_datareader and let the default role handle that.

    For execute, you sure you want to grant execute on every stored proc?  Take it all the procs in the database are just SELECT procs and nothing is an insert/update/delete proc?

    If so I would look at creating a "db_executor" role grant execute to the database level instead of individual objects.  But you may want to get very specific and create a role as you detailed and give only execute to the procs the reports need to run and not the full database, don't want it to be compromised and allow anyone to insert/update/delete as the report user now.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply