What minimum permissions and/or roles must a SQL Server login possess to be able to grant database db_datareader role to other logins?

To delegate the ability for a security admin to add new SQL logins to the db_datareader role within a database, what are the least permissions and/or roles the security admin must possess?

If this were a permission then you could use WITH GRANT OPTION, but it's not.

Assuming that you want to allow someone, or anyone in a particular Database-level Role, to perform this operation but not allow them to assign other Roles, then there is no permission or Role for that.

However, you can achieve this fine-grained "permission" via Module Signing. The minimum permission anyone needs is EXECUTE on a Stored Procedure that you write to do nothing more than add a Login or Role to that specific Database Role.

This is similar to what I outlined in the following answer:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

except in your particular case it is even easier since no server-level permissions are needed. Just do the following:

• Create a Stored Procedure that has an input parameter for the Principal name, and simply adds that Database Principal to the db_datareader Database-level Role

• Create a Certificate in that same DB

• Create a User, in that same DB, from that Certificate

• Add the Certificate-based User to the db_owner Database-level Role

• Sign the Stored Procedure (from Step 1) with that Certificate

• Grant any User who should be able to do this EXECUTE permission on that Stored Procedure

For the sake of completeness:

Technically speaking, you could add EXECUTE AS 'dbo' to the CREATE PROCEDURE statement, but that uses Impersonation which has issues of its own, and is less secure than the Module Signing approach I described above.

StackExchange Database Administrators Q#183792, answer score: 3