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 Databaseand
Alter Any Loginare 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
- preferably, without granting the CILogin Impersonation rights on all logins to the server