SQL Server Permissions – Changing DB Ownership Using a User with Limited Permissions

permissionssql server

At first, I would like to thank the community for creating such a resourceful forum. I've been using StackOverflow for years, and recently, using dba on SE too and you're great. Most of my questions have been asked for someone, or some comment have led me to an answer, so thank you very much!

After that being said, I have a problem which I don't think it's usual. The case is: I have been asked to create one database which will be a central db for a new system. This db will have the info that the company I work for need (like payment info, contacts, client's emails and stuff).

After that, each new customer will be given a clean db to work (the one they will use with our software). This db should have only one user, and the user can only see and connect to that specific database. This db should be partially contained and the user can't have any link to any existing instance login.
Also, for some reasons, the process of creating a db should be done over a procedure, to be triggered by some event, after the new client finishes his registration on our website. And the user that run this procedure (which I'll be calling "CREATOR") must not have any rights besides "db creation".

These steps I already covered, creating a user which only have permission to run the procedure and to create a database.

The problem is that this CREATOR user is the db_owner of the databases it creates. I would like to change the ownership to the sa (or any other login, really), but I can't because the CREATOR can't see anyone besides itself and the sa. I know that this happened because I didn't allow it to see other logins.

Then, it comes the question. How can I change the ownership of all created dbs to sa (the one I can see) without breaking those rules or how can I grant CREATOR the permission to see (and not to drop or create) other users and change the db owner of each db he creates?

Thanks in Advance!

Best Answer

May not be pretty, but you could use a trigger on database creation that executes as a privileged user of your choice:

CREATE TRIGGER [database_setup]

    ON ALL SERVER
    WITH EXECUTE AS 'privuser'
    FOR CREATE_DATABASE
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @db nvarchar(100)
    DECLARE @sql nvarchar(MAX)
    DECLARE @data XML
    SET @data = EVENTDATA()

    SELECT @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)');
    SELECT @sql = ('ALTER AUTHORIZATION ON DATABASE::' + @db + ' TO sa;');
    sp_executesql @sql;
    END
GO

ENABLE TRIGGER [database_setup] ON ALL SERVER
GO
Related Question