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?
4 Answers
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:
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.
-
Question is not about executing store procedure but it is about adding members in role that you are already a member. Aug 18, 2017 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. Aug 18, 2017 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.– GuessedAug 18, 2017 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. Aug 18, 2017 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.– GuessedAug 18, 2017 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://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.
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