0

I have created a server/instance level role called ReadOnly and assigned a user (windows based login) to it.

Now i'm trying to add a database level role with the same name where i can grant permissions on some database level objects, tables mainly.

However I can't see how to link the database level role to the server level role in SSMS.

1 Answer 1

3

Server roles are different than database roles because there are different privileges that can be granted at the server level vs the database level for objects that only exist in either respectively.

For example, the SQL Agent Jobs only exist at a server level, and stored procedures, views, and tables only exist at the database level, and therefore the permissions granted to those objects exist only at the same level.

It sounds like you just need to create the database role in each database you want to grant permissions for accordingly. I recommend reading Permissions (Database Engine) for more information.

5
  • Thanks, I thought though that the db and server level roles could be connected so that if i assign a bunch of users to the server level role then the database level roles that are linked to it automatically inherit that set of users and i don't have to add them all for every database level role. I've probably got it wrong though.
    – Paul
    Commented Jan 10, 2022 at 20:36
  • @Paul Not that I'm aware of, since again server level roles related to a certain set of permissions that are mutually exclusive from database level roles. There are some server level roles like SysAdmin that override database level roles, but outside of that I don't believe you can accomplish what you said in that manner. Rather you would need to create and manage each database level role within each database you want to grant permissions for.
    – J.D.
    Commented Jan 10, 2022 at 21:50
  • 1
    This is correct. Commented Jan 10, 2022 at 22:02
  • Thanks @J.D. i think i must have been thinking about grouping windows users together into a group at the operating system level then using that group of users within SQL server to sort out all of their permissions in 1 go.
    – Paul
    Commented Jan 11, 2022 at 17:43
  • @Paul No problem! Yes that's possible, SQL Server respects Active Directory Groups and can grant permissions to them, both at the database and server level. For access to database specific objects, you would need to grant those permissions to a "User" that represents the Active Directory Group you want to provision, within each database. Best of luck!
    – J.D.
    Commented Jan 11, 2022 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.