Minimum ALTER Permissions to scale Azure SQL Server

Question

I'm using the following query to scale up/down Azure SQL server programatically:

ALTER DATABASE [DB] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S3')

And I used this StackExchange Answer to give the DB user enough permissions to run the query:

GRANT ALTER ON DATABASE:: [DB] TO [username]

However I don't want to open up this user to have more database permissions than necessary.

Is there a way to only give ALTER access to modify the SERVICE_OBJECTIVE and not have any other heightened permissions?

Answer 1

You can encapsulate the ALTER DATABASE command in a stored procedure and sign it with a certificate that confers ALTER DATABASE permissions. This will permit minimally privileged users, with only execute permissions on the procs, to scale the database up/down even without ALTER DATABASE permission.

Example T-SQL script below. TL;DR, see Packaging Permissions in Stored Procedures.

--create certificate and cert user with necessary permission
CREATE CERTIFICATE AlterDatabaseCert
   ENCRYPTION BY PASSWORD = 'p%FD$4bb925DGvbd24^9587y'  
   WITH SUBJECT = 'For Alter Database User';   
GO  

CREATE USER AlterDatabaseUser
    FROM CERTIFICATE AlterDatabaseCert;
GO

GRANT ALTER ON DATABASE::[DB] TO AlterDatabaseUser;
GO

--create and sign stored procedures
CREATE OR ALTER PROC dbo.usp_ScaleUpDatabase
AS
ALTER DATABASE [DB]
    MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S12');
GO

ADD SIGNATURE to dbo.usp_ScaleUpDatabase
    BY CERTIFICATE AlterDatabaseCert
    WITH PASSWORD = 'p%FD$4bb925DGvbd24^9587y';
GO

CREATE OR ALTER PROC dbo.usp_ScaleDownDatabase
AS
ALTER DATABASE [DB] 
    MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S3');
GO

ADD SIGNATURE to dbo.usp_ScaleDownDatabase
    BY CERTIFICATE AlterDatabaseCert
    WITH PASSWORD = 'p%FD$4bb925DGvbd24^9587y';
GO

--grant execute permissions to minimally privileged users/roles
GRANT EXECUTE ON dbo.usp_ScaleDownDatabase TO [username];
GRANT EXECUTE ON dbo.usp_ScaleUpDatabase TO [username];
GO