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?”<\/p>\n
Bizarre and surreal conversations with myself aside, the answer was Yes, you sure could. The syntax is:<\/p>\n
ALTER AUTHORIZATION ON Database::<databaseName> TO <serverPrincipal><\/strong><\/p>\n To demonstrate, first, I will create a login:<\/p>\n create login test with password = ‘like, password, dude’ Then a database just taking all of the defaults. Setting an owner is not a part of the CREATE DATABASE syntax anyhow:<\/p>\n create database showChangeOwner Then, check the owner:<\/p>\n select SUSER_SNAME(owner_sid) This will return the login that you resolve to when you created the database. <\/p>\n MYDOMAIN\\LBDAVI <\/p>\n Next, run the following statement to change the owner: <\/p>\n ALTER AUTHORIZATION ON Database::showChangeOwner TO test<\/strong> <\/p>\n Then run the previous statement to see that the database is now owned by login: Test . <\/p>\n 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. <\/p>\n grant create any database to test Checking the owner again: <\/p>\n select SUSER_SNAME(owner_sid)
go<\/p>\n
go<\/p>\n
from sys.databases
where name = ‘showChangeOwner’ <\/p>\n
execute as login = ‘test’
create database showChangeOwner2 <\/p>\n
from sys.databases
where name = ‘showChangeOwner2’ <\/p>\n