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:
- I believe that
How can I resolve this, - without the CI scripts using an
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
Last activity: May 20, 2021, 02:33 AM