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)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.