Server-level roles required for perform the "ALTER ROLE ADD MEMBER" query

Question

Hi, I want to create a login credential in SQL server with the following capabilities:

1. It should be able to access any database on the SQL server. This can be achieved by assigning the ##MS_DatabaseManager## and ##MS_DatabaseConnector## server-level roles to the login.
2. Once logged into a database, it should have permission to create a user(User1) from an external provider. This is also possible with the roles mentioned above.
3. Finally, I want to grant the db_datareader permission to that User1 by the help of that login, but I'm encountering difficulties assigning this role to the created User1.

Is there any server-level role that can be assigned to a login to allow execution of the following query: ALTER ROLE db_datareader ADD MEMBER user1?

Please guide me on how to achieve this. Thanks.

Answer 1

I'm afraid that would require a quite hefty permission.

A possible solution is to put the action in question in a stored procedure that you sign with a certificate. Then you create a login from that certificate and grant that login CONTROL SERVER. Then you have packaged the permission in the procedure itself.

I describe this technique in detail in this article: https://www.sommarskog.se/grantperm.html.

Answer 2

@Devendra Parmar Thank you for reaching out.

To run this command, you need one or more of these permissions or memberships:

• ALTER permission on the role
• ALTER ANY ROLE permission on the database
• Membership in the db_securityadmin fixed database role

Additionally, to change the membership in a fixed database role you need:

Membership in the db_owner fixed database roleTo run this command you need one or more of these permissions or memberships:

• ALTER permission on the role
• ALTER ANY ROLE permission on the database
• Membership in the db_securityadmin fixed database role

Additionally, to change the membership in a fixed database role you need:

• Membership in the db_owner fixed database role.

USE [YourDatabase];
GRANT ALTER ANY ROLE TO [YourLogin];

Then

USE [YourDatabase];
ALTER ROLE db_owner ADD MEMBER [YourLogin];

Regards,

Oury