Cannot find role db_datareader

Question

I am logged in as sa (double checked it in my session with select suser_name()).

I have created a login cards_login_generator, and the same-named user on the database [cards]. I want to permit this user to create logins,users, and make them members to db_datareader and db_datawriter.

I've granted ALTER ANY LOGIN/USER and it works. Now the time comes for:

grant alter on role::[db_datareader] to cards_login_generator

which fails with:

Cannot find the role 'dbo.db_datareader', because it does not exist or you do not have permission.

I have searched exhaustively around but cannot imagine any reason for this message. The role is a (probably undeletable?) database role, and I am sa so no permission issue could be the case. Why does this fail?

Microsoft SQL Server 2017 (RTM-GDR) (KB5014354) - 14.0.2042.3 (X64)

Answer 1

As mentioned in this answer, modifying membership in a fixed database role requires the modifying principal to be a member of the db_owner fixed database role.

Here's a test-bed to show how this works. First off, we'll create a login that has permission to create other logins, then we'll add that login to our [Test] database where we'll create a user for the login, and grant that user permission to alter users and roles.

USE [master];
GO
CREATE LOGIN [user_creator_test] 
WITH 
 PASSWORD = N'SomeExceptionally
StrongPassword5%'
 , DEFAULT_LANGUAGE = us_english
 , DEFAULT_DATABASE = [Test];
GRANT ALTER ANY LOGIN TO [user_creator_test];
GO
USE [Test];
GO
CREATE USER [user_creator_test]
FOR LOGIN [user_creator_test]
WITH DEFAULT_SCHEMA = [dbo];
GRANT ALTER ANY USER TO [user_creator_test];
GRANT ALTER ANY ROLE TO [user_creator_test];
GO

Next, we'll impersonate the new login and attempt to create a new login and user, and add that user to the fixed database roles, db_datareader and db_datawriter:

EXECUTE AS LOGIN = N'user_creator_test';
CREATE LOGIN [test_login] 
WITH PASSWORD = N'SomeCrazyHardToGuessPasswords27%'
 , DEFAULT_DATABASE = [Test]
 , DEFAULT_LANGUAGE = us_english;
CREATE USER [test_login_user]
FOR LOGIN [test_login]
WITH DEFAULT_SCHEMA = [dbo]
ALTER ROLE db_datareader ADD MEMBER [test_login_user];
ALTER ROLE db_datawriter ADD MEMBER [test_login_user];
REVERT;

The ALTER ROLE statements above fail, reporting this error:

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

However, if we make the [user_creator_test] a member of the db_owner role in our [Test] database, then re-run the test, it succeeds:

ALTER ROLE db_owner ADD MEMBER [user_creator_test];
GO
EXECUTE AS LOGIN = N'user_creator_test';
SELECT USER_NAME(), DB_NAME();
ALTER ROLE db_datareader ADD MEMBER [test_login_user];
ALTER ROLE db_datawriter ADD MEMBER [test_login_user];
REVERT;
SELECT USER_NAME();
GO

This query displays database role membership:

SELECT 
 [role] = dpr.[name]
 , [member] = dpm.[name]
FROM 
 [sys].[database_principals] dpm
 INNER JOIN [sys].[database_role_members] drm ON dpm.[principal_id] = drm.[member_principal_id]
 INNER JOIN [sys].[database_principals] dpr ON drm.[role_principal_id] = dpr.[principal_id]
ORDER BY 
 [role]
 , [member];

Results:

role	member
db_datareader	test_login_user
db_datawriter	test_login_user
db_owner	dbo
db_owner	user_creator_test

Cleanup:

USE [master];
GO
IF EXISTS
(
 SELECT 1
 FROM [sys].[server_principals] sp
 WHERE sp.[name] = N'test_login'
)
BEGIN
 DROP LOGIN [test_login];
 PRINT N'Dropped login [test_login]';
END
GO
USE [Test];
GO
IF EXISTS
(
 SELECT 1
 FROM [sys].[database_principals] dp
 WHERE dp.[name] = N'test_login_user'
)
BEGIN
 DROP USER [test_login_user];
 PRINT N'Dropped user [test_login_user]';
END
GO
USE [master];
GO
IF EXISTS
(
 SELECT 1
 FROM [sys].[server_principals] sp
 WHERE sp.[name] = N'user_creator_test'
)
BEGIN
 DROP LOGIN [user_creator_test];
END
GO
USE [Test];
GO
IF EXISTS
(
 SELECT 1
 FROM [sys].[database_principals] dp
 WHERE dp.[name] = N'user_creator_test'
)
BEGIN
 DROP USER [user_creator_test];
END
GO

Answer 2

According to the ALTER ROLE doc, in order to be able to alter the membership of a fixed database role a user must also be member of the db_owner on the database:

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

• Membership in the db_owner fixed database role