I need to set up some custom database roles within Microsoft SQL Server 2014 for reporting purposes. I need to set up the following roles:
- Administrative Reporting Role
- Read-only access to some subset "A" of all tables/views that will not contain tables with various sensitive info (hence it cannot simply be the db_datareader role).
- Can update Standard Reporting Role, granting/removing access to tables within subset "A"
- Standard Reporting Role
- Read-only access to some subset "B" of all tables in subset "A"
- Can never gain access to tables outside of subset "A"
- Can never gain higher level privileges than Administrative Reporting Role has
So far, for the Administrative Reporting Role, I simply created a new database role, set its owner to db_datareader, and then used the Securables section of the UI to grant Select access to a few tables. I then tried creating a new database role with the Administrative Reporting Role as the owner, but when I assigned test users to the role, the user in the Administrative Role was unable to grant access to the user in the Standard Role. Is there a way to achieve this, or perhaps a built-in feature I should be using instead?
Additionally, when granting access to tables, is there a good way to add a lot at once (I'm guessing via query as opposed to the UI)?