Minimal Permissions to Create an Application Database and Database Owner

Question

I wish to write (or borrow if you have one) a script template for creating an application database and associated logins. The script is to be run in CI pipelines and on developer desktops.

Possibly influenced by postgres but also by MS maybe-saying that assigning ownership to a disabled login is best practice I wish to assign ownership of the database to some insignificant login created for the purpose:

Create Login appname_owner With Password = 'randomlygeneratedpassword';
Alter Login appname_owner Disable
Create Database appname  ;
Alter Authorization On database::appname To appname_owner ;

Question: What is the minimal set of permissions that will allow a CI login to do this?

• I believe that Create Any Database and Alter Any Login are minimal for create login & database. [ I can't say I'm happy with CI scripts needing Alter Any Login. It would be nicer to restrict alterable Logins to some group or role defined for the purpose.]
• But I'm stumped on Alter Authorization. It seems I need Grant Impersonate On Login::appname_owner To CILogin, which the CILogin can't grant to itself, not even for a login it has just created.

How can I resolve this,

• without the CI scripts using an sa login and
• preferably, without granting the CILogin Impersonation rights on all logins to the server

?

Answer 1

That can be achieved by creating a user based on a certificate and Signing a stored procedure by using a certificate:

USE master;
GO

CREATE CERTIFICATE MyInpersonationCert   
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
   WITH SUBJECT = 'Certificate to do a test',   
   EXPIRY_DATE = '20261031';  
GO 

CREATE LOGIN login_from_cert FROM CERTIFICATE MyInpersonationCert;
GO

--It might be a little bit too much permission, but I'm tired and it's late at night.
--You'll figure out the proper privileges for this login ;)
ALTER SERVER ROLE sysadmin ADD MEMBER login_from_cert;
GO

With the login from the certificate having the privileges to create and manipulate the objects you need you can create the procedure.

CREATE PROCEDURE impersonate_procedure
    @database_name nvarchar(128),
    @login_name nvarchar(128),
    @login_password nvarchar(128)
AS
BEGIN
        
    --add some verification on the received parameters to avoid SQL Injection (still tired)
    
    DECLARE @sql nvarchar(max);
    
    SET @sql = '
        CREATE LOGIN ' + @login_name + ' WITH PASSWORD = ''' + @login_password + ''';
        ALTER LOGIN ' + @login_name + ' DISABLE;
        CREATE DATABASE ' + @database_name + ';
        ALTER AUTHORIZATION ON database::' + @database_name + ' TO ' + @login_name + ';';
    
    EXECUTE sp_executesql @sql;
    
END;
GO

ADD SIGNATURE TO dbo.impersonate_procedure
    BY CERTIFICATE MyInpersonationCert
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO 

Now you can grant execute to the login used for the CI scripts on dbo.impersonate_procedure. Your login will be able to execute the procedure, but not to explicitly issue a CREATE DATABASE or ALTER AUTHORIZATION.