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?

up vote 3 down vote accepted

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.

  • Question is not about executing store procedure but it is about adding members in role that you are already a member. – SqlWorldWide Aug 18 '17 at 11:36
  • @SqlWorldWide I just added some verbiage at the top of my answer to clarify that I was addressing the intent of the question, not the literal wording. The O.P. certainly appears to want to allow someone to allow others into a specific Role instead of having the ability to add to all Roles, or all user-defined Roles. – Solomon Rutzky Aug 18 '17 at 14:34
  • 1
    @Solomon Rutzky Correctly interpreted. The trick is now to create a single stored proc in master that dynamically empowers the restricted security admin to be able to add a login to the db_datareader role in any database. – Guessed Aug 18 '17 at 18:23
  • @Guessed Well, that assumes that the Login has a corresponding User in that DB. But either way, that is still easy. Just needs to be Dynamic SQL and needs to be a server-level permission. There might be a server-level role for security that allows for this. Else might need to use sysadmin. Just be sure to check the input params for DatabaseName and UserName to prevent SQL Injection. – Solomon Rutzky Aug 18 '17 at 18:31
  • @Solomon Rutzky sysadmin is not permitted. The intent is to empower help desk techs with the ability to add either Windows or SQL logins, map them to dbs, and add these db users to the db_datareader role only, in any db on a SQL Server. All this, while containing their powers to a minimum. – Guessed Aug 18 '17 at 19:18

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://docs.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.

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

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.