Originally I set up a login user to run SSRS reports.I granted execute rights to some stored procs, etc., and proper table level rights for tables. Everything works well.
Once I started writing more and more reports that need more and more procs and tables, I realized I should grant the login user a certain role so that the procs and tables inherit the correct rights. Then I wouldn't have to keep granting rights at the table level, etc.
Is there a best practice for doing this? I'm fairly new to basic admin, so I'd like to see if this is possible.
I'd be grateful for any advice at all!
Creating a role would work - but if this is the only user that would ever be added to the role then there is no real reason to go to that level.
Either way - you need to grant permissions at the appropriate level. If that report login/user will only be executing defined stored procedures, then create a separate schema (ssrs for example) and grant access to everything in that schema:
GRANT EXECUTE ON schema::ssrs TO {reportuser};
GRANT SELECT ON schema::ssrs TO {reportuser};
Now - if someone creates a table or procedure in the ssrs schema - reportuser automatically gets access to that new table or procedure. As long as the procedures do not access anything outside this database - then further permissions are not needed.
If the procedures (and views) access data in other databases - then reportuser would need to be added to those databases with appropriate permissions.
With this in mind - you need to be very sure that this account is only utilized from SSRS and that all reports published have been reviewed so they don't allow unwanted/unsecured access to the data.