Changing db ownership using a user which can only see the sa

Question

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!

Answer 1

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

• ACustodio: Thanks John, with some little modifications I achieved the goal. I had to use CREATE_USER instead of CREATE_DATABASE, because after that, I couldn't add the tables to the created db, and add a USER is the last thing the script does. Also, I took the liberty of editing your post a little (there was two "DATABASE" on ALTER AUTHORIZATION statement and the variables needed to be nvarchar). Thanks again!
• LowlyDBA - John M: Ah, thanks. I was editing an old script I had and obviously didn't do a very good job! Glad it helped.