Sample Header Ad - 728x90

Minimal Permissions to Create an Application Database and Database Owner

2 votes
1 answer
89 views
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 ?
Asked by Chris F Carroll (214 rep)
May 19, 2021, 11:08 AM
Last activity: May 20, 2021, 02:33 AM