Split Brain Administrators

Question

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

These 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 sa account.

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 AUTHENTICATION to change the database owner to the generic, low-privilege account

The databases are all on the same server and same instance.

Answer 1

I would use a server level trigger on CREATE DATABASE.

Something like this:

CREATE TRIGGER [CreateDatabase_OwnerMapper]
    ON ALL SERVER 
    WITH EXECUTE AS 'sa'
    FOR CREATE_DATABASE
    AS
    BEGIN

    SET NOCOUNT ON;

    DECLARE @DatabaseName NVARCHAR(128) = 
    (SELECT CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVARCHAR(128)));

    DECLARE @SQL NVARCHAR(MAX) = '';
    SET @SQL = 'ALTER AUTHORIZATION ON DATABASE::' + @DatabaseName + ' TO sa';

    EXEC @SQL;

END

This will fire off a trigger to automatically change the owner whenever the CREATE DATABASE command is issued.

You could grant the db_owner yourself with the trigger potentially. I do this on our debt environments.