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

Question

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?

Answer 1

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:

1. 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
2. Create a Certificate in that same DB
3. Create a User, in that same DB, from that Certificate
4. Add the Certificate-based User to the db_owner Database-level Role
5. Sign the Stored Procedure (from Step 1) with that Certificate
6. 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.

Answer 2

Per Microsoft documentation on database level roles a user within a database must be a member of either the db_owner or db_securityadmin roles to execute ALTER ROLE commands.

It is also possible per https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql to grant the permission of ALTER on a role, or ALTER ANY ROLE to allow membership to be adjusted.

Answer 3

In order to change membership for a fixed database role you have to be a member of db_owner fixed database role.

In order to change membership for a user-defined database role as a minimum you need to have alter any role privilege.

It will be a long example but hopefully you can follow this.

Set up.

USE [Master]
GO
CREATE DATABASE [TestPerm];
GO
CREATE LOGIN testPerm1 WITH PASSWORD = 'testPerm1###',DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE LOGIN testPerm2 WITH PASSWORD = 'testPerm2###',DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE  [TestPerm]
GO
CREATE USER testPerm1;
GO
CREATE USER testPerm2;
GO
ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm1;
GO
GRANT ALTER ANY ROLE  TO testPerm1;
GO

Now run as testPerm1 user and try to add testPerm2 user to the fixed database role db_datareader.

USE  [TestPerm]
GO
EXECUTE AS USER='testPerm1'
SELECT SUSER_NAME(), USER_NAME();  
ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm2;
REVERT;

Getting the following error:

Msg 15151, Level 16, State 1, Line 21 Cannot alter the role 'DB_DATAREADER', because it does not exist or you do not have permission.

Add user testPerm1 as a member of db_securityadmin role and try to add testPerm2 user to the fixed database role db_datareader. You will get the same error as previous step.

 USE [testPerm]
 GO
 ALTER ROLE [db_securityadmin] ADD MEMBER testPerm1;
 GO
 EXECUTE AS USER='testPerm1'
 SELECT SUSER_NAME(), USER_NAME();  
 ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm2;
 REVERT;

Msg 15151, Level 16, State 1, Line 21 Cannot alter the role 'DB_DATAREADER', because it does not exist or you do not have permission.

Adding testPerm1 user as a member of db_owner fixed database role.

USE [testPerm]
GO
ALTER ROLE [db_owner] ADD MEMBER testPerm1;
GO

Now try to add testPerm2 user to the fixed database role db_datareader. Command will succeed.

 USE [testPerm]
 GO
 EXECUTE AS USER='testPerm1'
 SELECT SUSER_NAME(), USER_NAME();  
 ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm2;
 REVERT;

Lets check the permission required to add members to user-defined database role.

Create a new role and add testPerm1 user to this role.

USE [TestPerm]
GO
CREATE ROLE [testPermission]
GO
ALTER ROLE [DB_DATAREADER] ADD MEMBER testPerm1;
GO

Remove testPerm1 user from db_owner and db_securityadmin role.

USE [TestPerm]
GO
ALTER ROLE [db_owner] DROP MEMBER testPerm1;
GO
ALTER ROLE [db_securityadmin] DROP MEMBER testPerm1;
GO

Try to modify user defined database role testPermission by user testPerm1 and it will be successful because testPerm user still had alter any role privilege.

EXECUTE AS USER='testPerm1'
SELECT SUSER_NAME(), USER_NAME();  
ALTER ROLE [testPermission] ADD MEMBER testPerm2;
REVERT;

Revoke alter any role privilege for testPerm1 user.

USE [TestPerm]
GO
REVOKE ALTER ANY ROLE  TO testPerm1;
GO

Try to modify user defined database role testPermission by user testPerm1, you get the error message:

EXECUTE AS USER='testPerm1'
SELECT SUSER_NAME(), USER_NAME();  
ALTER ROLE [testPermission] DROP MEMBER testPerm2;
REVERT;

Msg 15151, Level 16, State 1, Line 3 Cannot alter the role 'testPermission', because it does not exist or you do not have permission.

Clean up code.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestPerm'
GO
USE [master]
GO
ALTER DATABASE [TestPerm] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [TestPerm]
GO
DROP LOGIN [testPerm1]
GO
DROP LOGIN [testPerm2]
GO

Answer 4

I could comment on all the above answers one by one but I think i'd better write here one compact answer. The question is about adding a user to a FIXED DATABASE ROLE db_datareader and that is the key. From BOL we know that

Adding members to fixed database roles requires membership in the db_owner fixed database role.

And since the OP asked about the least permission, and membership in db_owner database role is the maximum you can have within a database, the only possible solution here is signed procedure. So I upvote Solomon Rutzky answer