Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.