I have been appointed the DBA for our organization. I have been asked by senior officials to allow the two divisions of IT to have full control of their databases. They have asked for the following layout:
Admins SubAdmin SubAdmin
SubAdmins should be capable of performing all the functions possible on their own database, but should not be able to perform any functions on any other database.
That does not include server-level things like killing a session, building a logon trigger, changing Resource Governor settings, etc, but they should be able to create databases.
I have created a server role for the SubAdmin group which grants
CREATE ANY DATABASE. This works well, except that when the SubAdmin attempts to change the owner of the newly created database to
sa. They cannot because they cannot impersonate the
Would it be easier just to write some powershell script that changes the db owner to
sa each night?
I use the
sa account in this example just as a demonstration. We want the database owner to be a generic, low-privilege account. Therefore when the SubAdmin creates a database they:
- Create the db
- Grant their admin group db_owner rights
- then should perform
ALTER AUTHENTICATIONto change the database owner to the generic, low-privilege account
The databases are all on the same server and same instance.