Sql-server – Stored Procedure Signed by Certificate Unable to Add Members to Database Roles

I created a Stored Procedure that performs the Following tasks:

• Creates database
• Change owner to sa
• Set recovery model
• Creates users in database
• Adds users to database roles.
• Grants execute and view definition to users

I then created a certificate, Login from cert, user from cert, granted exec on SP to user, granted control server to login, added signature to sp by certificate.

When non sysadmins execute the SP everything works except for adding users to the database roles with error: Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.

If I add the login to the sysadmin server role, when non sysadmins execute the SP it will work. Ideally the login would have minimal permissions to perform these functions. Is it possible to do this w/o granting sysadmin to the user?

Here is the code I used to create the certificate, login, user, signature, etc:

use master;
go

CREATE CERTIFICATE sp_CreateNewReportingDatabase_cert
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH SUBJECT = 'Certificate for sp_CreateNewReportingDatabase',
    START_DATE = '20201015', EXPIRY_DATE = '21000101'
GO

CREATE LOGIN sp_CreateNewReportingDatabase_cert_user FROM CERTIFICATE sp_CreateNewReportingDatabase_cert;
GO
CREATE USER sp_CreateNewReportingDatabase_cert_user FROM CERTIFICATE sp_CreateNewReportingDatabase_cert;
GO
GRANT EXECUTE ON dbo.CreateNewReportingDatabase TO sp_CreateNewReportingDatabase_cert_user;
GO
GRANT CONTROL SERVER TO sp_CreateNewReportingDatabase_cert_user;
GO
ADD SIGNATURE TO [dbo].[CreateNewReportingDatabase] BY CERTIFICATE sp_CreateNewReportingDatabase_cert
WITH PASSWORD = 'SuperSecretPassword';
GO

Here is the code to create the stored proc:

CREATE OR ALTER PROC dbo.CreateNewReportingDatabase @DatabaseName NVARCHAR(50), @ReportingPod NVARCHAR(50) = NULL
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @SqlCommand NVARCHAR(2000);

--Error Checking
IF @ReportingPod NOT LIKE 'TAC[_]NT\Analysis-%' AND (@DatabaseName LIKE 'Reporting[_]%' OR @DatabaseName LIKE '[_]Salesforce')
    BEGIN
        RAISERROR('For Reporting_<client> and <client>_Salesforce databases you must populate @ReportingPod with a value that follow formate TAC_NT\Analysis-<reportingpod> Contact DBA',16,1)
        SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
    END
ELSE IF (@DatabaseName LIKE 'Reporting[_]%' OR @DatabaseName LIKE '[_]Salesforce') AND @ReportingPod IS NULL
    BEGIN
        RAISERROR('For Reporting_<client> and <client>_Salesforce databases you must populate @ReportingPod with a value that follow formate TAC_NT\Analysis-<reportingpod> Contact DBA',16,1)
        SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.        
    END
ELSE IF EXISTS (
    SELECT [name]
    FROM sys.databases
    WHERE [name] = @DatabaseName
    )
    BEGIN
        RAISERROR('There is already a detabase with that name!',16,1)
        SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
    END
    
--Execute Scrips
ELSE
BEGIN
    --Create the database
    PRINT 'Creating database ' + @databasename;
    SET @SqlCommand = N'CREATE DATABASE [' + @DatabaseName + ']'    
    EXEC dbo.sp_executesql @SqlCommand

    -- change owner to sa
    PRINT 'Switching owner to sa';
    SET @SqlCommand = N'ALTER AUTHORIZATION ON DATABASE::[' + @DatabaseName + '] TO [sa];'
    EXEC dbo.sp_executesql @SqlCommand;
    
    -- set recovery model to simple
    IF @DatabaseName LIKE 'Reporting[_]%' OR @DatabaseName LIKE '%[_]Salesforce'
        BEGIN
            PRINT 'Setting recovery model to simple';
            SET @SqlCommand = N'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE;'
            EXEC dbo.sp_executesql @SqlCommand
        END
    ELSE PRINT 'Revoery model not modified from default';

    -- Create users for ServiceAccount and reportingPod in Reporting_ databases.
    IF @DatabaseName LIKE 'reporting[_]%' OR @DatabaseName LIKE '%[_]Salesforce'
        BEGIN
            SET @SqlCommand = N'
                USE [' + @DatabaseName + ']
                IF NOT EXISTS(
                    SELECT [name]
                    FROM sys.database_principals
                    WHERE
                        [type] NOT IN (''s'',''r'')
                        AND [NAME] = ''TAC_NT\ServiceAccount''
                )
                BEGIN
                    PRINT ''Creating [TAC_NT\ServiceAccount] user''
                    CREATE USER [TAC_NT\ServiceAccount] FOR LOGIN [TAC_NT\ServiceAccount]
                END         

                IF NOT EXISTS(
                    SELECT [name]
                    FROM sys.database_principals
                    WHERE
                        [type] NOT IN (''s'',''r'')
                        AND [NAME] = ''' + @ReportingPod + '''
                )
                BEGIN
                    PRINT ''Creating ' + @ReportingPod + ' user''
                    CREATE USER [' + @ReportingPod +  '] FOR LOGIN [' + @ReportingPod + '];
                END'
            EXEC sp_executesql @SqlCommand;
        END
    ELSE PRINT 'Skipping the creation of the ServiceAccount and Reporting POD database principals'

    ---Grant role membership for ServiceAccount
    IF @DatabaseName LIKE 'Reporting[_]%'
    BEGIN
        PRINT 'Adding !gampelan to roles: reader, writer, ddladmin';
        SET @SqlCommand = N'
        USE [' + @DatabaseName + ']
        ALTER ROLE db_datareader ADD MEMBER [TAC_NT\ServiceAccount]
        ALTER ROLE db_datawriter ADD MEMBER [TAC_NT\ServiceAccount]
        ALTER ROLE db_ddladmin ADD MEMBER [TAC_NT\ServiceAccount]
        '
        EXEC sp_executesql @SqlCommand
    END
    ELSE IF @DatabaseName  LIKE '%[_]salesforce'
    BEGIN
        PRINT 'Adding ServiceAccount to roles: reader';
        SET @SqlCommand = N'
        USE [' + @DatabaseName + ']
        ALTER ROLE db_datareader ADD MEMBER [TAC_NT\ServiceAccount]
        '
        EXEC sp_executesql @SqlCommand
    END


    --Grant role membership for ReportingPod
    IF @DatabaseName  LIKE 'Reporting[_]%' AND @ReportingPod LIKE 'TAC_NT\Analysis%'
    BEGIN
        PRINT 'Adding ' + @ReportingPod + ' to roles: reader';
        SET @SqlCommand = N'
        USE [' + @DatabaseName + ']
        ALTER ROLE db_datareader ADD MEMBER [' + @ReportingPod + ']'
        EXEC sp_executesql @SqlCommand
    END
    ELSE IF @DatabaseName  LIKE '%salesforce' AND @ReportingPod LIKE 'TAC_NT\Analysis%'
    BEGIN
        PRINT 'Adding ' + @ReportingPod + ' to roles: reader';
        SET @SqlCommand = N'
            USE [' + @DatabaseName + ']
            ALTER ROLE db_datareader ADD MEMBER [' + @ReportingPod + ']'    
        EXEC sp_executesql @SqlCommand
    END
    
    --Grant execute and view definition for ReportingPod
    IF @DatabaseName  LIKE '%[_]Salesforce' OR @DatabaseName  LIKE 'Reporting[_]%'
    BEGIN
        PRINT 'Granting execute and view definition to ' + @ReportingPod;   
        SET @SqlCommand = N'
        USE [' + @DatabaseName + ']
        GRANT EXECUTE TO [' + @ReportingPod + '];
        GRANT VIEW DEFINITION TO [' + @ReportingPod + '];'  
        EXEC dbo.sp_executesql @SqlCommand;
    END
END
GO