MS Sql Server link a database level role to a server level role

Posted on

Question :

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.

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *