2

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

?

0
3

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.

6
  • Thanks for this! Yes, I think this puts me on the right track. I think for the verification the simplest might be a server role for 'is a CI login' and another server role for 'logins that CI is allowed to impersonate inside this procedure'. I'll work on that line. May 21 at 9:47
  • Is signing a procedure 'the' way to make an sp execute with borrowed permissions — was there no way to do this before certificates? May 21 at 9:49
  • @ChrisFCarroll, glad I could help. No, it's not the only way to borrow privileges. Another possible way to do it (and one I struggled to use and couldn't make it work for your request) is to use EXECUTE AS clause on the create procedure statement. See the post Execute permission denied on object sp_start_job, it helped me to achieve this solution.
    – Ronaldo
    May 21 at 10:29
  • @ChrisFCarroll, were you able to solve your problem with this or any other method?
    – Ronaldo
    Jun 17 at 11:51
  • Hi, Yes! Sorry for the delayed acceptance: I was trying to do something slightly different to what you proposed and couldn't get exactly what I wanted. However, your approach works and answers the problem Jun 18 at 21:33

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.