0

Is it possible to grant database access to a user-defined server role?

My login test-login has the server role myServerRole. I have set up the table-level permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[ConfigSettings] TO [myServerRole];

but test-login cannot connect to the database:

USE [MyTestDb]

Msg 916, Level 14, State 2, Line 1
The server principal "test-login" is not able to access the database "MyTestDb" under the current security context.

Background: In our system we have many databases, 1 per customer, all with a similar structure. Each database contains a ConfigSettings table.

I want to allow a particular application full read/write access to this table only.

I know I could do this by creating a database user for this login in each database, and then granting permission to that database user. But it feels like I could save a few lines of code if could assign permissions directly to the user-defined server role.

1

1 Answer 1

1

It seems this is not possible. According to this answer, server roles are only intended for server-level permissions.

1
  • 1
    Correct. If you wanted to share a server level permission to multiple database Users, then you can map a User of each database to the same Login who has that permission provisioned. But you're trying to essentially do the opposite, which is provision database level permissions to a Login, so you don't have to repeat yourself for each User of each database. Unfortunately, as far as I know (even after almost a year later), nothing has changed since I originally wrote that answer.
    – J.D.
    Nov 14, 2022 at 15:39

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.