0

What's the query to set a login user as db_owner during the creation of database. Following is the set of queries, which I'm using right now,

Create database Test1;
ALTER AUTHORIZATION ON DATABASE::Test1 TO [DEVELOPMENT\d-testuser];

This servers my purpose, but here I have firstly created a database with default db_owner and then altered it. I need a query which will select [DEVELOPMENT\d-testuser] as my database owner during the creation of database itself.

2 Answers 2

2

You could set the owner of model database as [DEVELOPMENT\d-testuser] and then any new databases that are created will automatically be owned by [DEVELOPMENT\d-testuser].

I normally tend to set the database owner to sa. Our applications are not using (should never use) sa :-) so there is no harm in setting the db owner to sa.

0

There seems to be no way of accomplishing this in one statement. You have to use alter authorization afterwards.

The two tricky ways I can think of are:
You can set the Model database's owner to [DEVELOPMENT\d-testuser].
Or, Log in with the login you want database owner to be.

The owner is the user that creates the database. The database owner can be changed by using sp_changedbowner.

https://msdn.microsoft.com/en-CA/library/ms176061.aspx?f=255&MSPPError=-2147217396

2
  • sp_changedbowner is announced deprecated. Alter Authorization .. is the way to go.
    – Kin Shah
    Jun 30, 2016 at 18:11
  • I was quoting msdn as is. Thanks for pointing out though..
    – GaganLamba
    Jul 1, 2016 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.