Tonight, as I was creating my sample database for my chapter on implementing the database, I learned something new, that existed in 2005.  I had always used sp_changedbowner to change the owner of a database, but I was reading in another section about ALTER AUTHORIZATION earlier to change the owner of an object in a schema, so I said to my self “Louis” (I call myself that) “Louis, wonder if ALTER AUTHORIZATION works on other stuff?”

Bizarre and surreal conversations with myself aside, the answer was Yes, you sure could.  The syntax is:

ALTER AUTHORIZATION ON Database::<databaseName> TO <serverPrincipal>

To demonstrate, first, I will create a login:

create login test with password = ‘like, password, dude’
go

Then a database just taking all of the defaults.  Setting an owner is not a part of the CREATE DATABASE syntax anyhow:

create database showChangeOwner
go

Then, check the owner:

select SUSER_SNAME(owner_sid)
from   sys.databases
where  name = ‘showChangeOwner’

This will return the login that you resolve to when you created the database. 

MYDOMAIN\LBDAVI

Next, run the following statement to change the owner:

ALTER AUTHORIZATION ON Database::showChangeOwner TO test

Then run the previous statement to see that the database is now owned by login: Test .

As an aside, if you use EXECUTE AS to change context to a different login, any database you create will be created as the user you are EXECUTING AS, so this could technically be the way to set the owner at creation time.  I will give the test login rights to create a database then execute as it. (My new favorite permission is “create any database”.  I looked, but despite the connotation, there is not a “create almost any database”, “create just this one database”, or even “create database named fred” rights.

grant create any database to test
execute as login = ‘test’
create database showChangeOwner2

Checking the owner again:

select SUSER_SNAME(owner_sid)
from   sys.databases
where  name = ‘showChangeOwner2’

Will show that this is owned by user “test”. ALTER AUTHORIZATION will actually let you change the owner of quite a few different things.  For more information, please visit your public library…wait, no this is 2007, not 1970…  Check here in books online.

Don’t forget to clean up!

drop database showChangeOwner, showChangeOwner2

(did you know you could do that?  Saved me having to type drop database two times.  Wow, the time I saved by not typing drop database.  Wait, I typed drop database even more times after that.  And yes, this obvious lack of material is why I haven’t been blogging of late…)