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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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