0

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
3
  • please add the text to your question and don't use links.
    – nbk
    Oct 15, 2020 at 21:34
  • I attempeted to do that, but the code block kept getting broken due to having ''' in the actual code. Is there a better way to add a code block that won't escape with ''' ?
    – Casey
    Oct 15, 2020 at 22:54
  • @nbk I figured out you can do code block with 3 '~'. Thanks for the advice.
    – Casey
    Oct 15, 2020 at 23:19

1 Answer 1

0

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.

According to the ALTER ROLE documentation:

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

  • Membership in the db_owner fixed database role

The user executing the proc was initially the database owner (the dbo user is a db_owner role member) after the database was created but the proc code changed the owner to sa. The user no longer had permissions to alter fixed database roles from that point forward.

Normally I would suggest change the database authorization as the last step so the user would have all the needed permissions. The tricky part here, though, is the proc also adds the caller as a database user, which is problematic because the user is dbo until the database owner is changed.

Given the certificate user has CONTROL SERVER permissions, you can EXECUTE AS LOGIN 'sa'; after changing the database owner. This way, the caller doesn't require sysadmin role membership and is limited to the elevated functionality in the stored procedure.

3
  • I attempted the EXECUE AS LOGIN 'sa'; However, the 'sa' login is disabled. I tested moving the database authorization step to the end of the stored proc, and it worked. I checked the users of the database after creation and the calling user was not listed. Would you mind expanding on your concern for not taking that approach?
    – Casey
    Oct 16, 2020 at 15:16
  • .@Casey, I have no concerns about that approach. I made the assumption the caller was the same as the @ReportingPod parameter so it seems that not the case if moving the alter authorization to the end fixed it. The issue with the caller being the same as the user to be added is that the database owner cannot also be added as a user in the database.
    – Dan Guzman
    Oct 16, 2020 at 16:15
  • The calling user will be the TAC_NT\ServiceAccount user so we will run into that issue. I suppose we could create a distinct account to execute this stored proc though. With the sa account being disabled I'm assuming that the execute as approach won't work. Thanks for the help.
    – Casey
    Oct 16, 2020 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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