Fact: every SQL Server database has an “owner”. You can check the owner of a database by running this query:

SELECT NAME, SUSER_SNAME(owner_sid) 
FROM   sys.databases 
WHERE NAME = 'DatabaseName'

However, there may come a day when you run into this error:

There was error outputting database level information for ServerName.DatabaseName.

Property Owner is not available for Database ‘[DatabaseName]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

When you log into SQL Server Management Studio, right-click the database, and select Properties, you’ll see this dialog box:

Without a database owner, you can’t view the database properties. You may run into issues executing sp_helpdb. You may get an error using “EXECUTE AS OWNER”. How do you fix this?

The Old Way: sp_changedbowner

Normally, I would use sp_changedbowner. Why? It’s familiar. It’s comfortable, like my favorite socks. But there’s a new way, and it’s time for me to learn that. (Also, Microsoft has indicated it will be removed in a future version.)

The New Way: ALTER AUTHORIZATION

I had to fumble around a bit to find this command. I am familiar with using ALTER DATABASE SET… to change many database facets. However, in looking through Books Online, I didn’t see a way to use this to change the database owner. I dug a little further, and found ALTER AUTHORIZATION.

The BOL syntax is:

`ALTER AUTHORIZATION
ON [ class_type:: ] entity_name
TO { SCHEMA OWNER | principal_name }

class_type ::=
{
OBJECT | ASSEMBLY | ASYMMETRIC KEY | CERTIFICATE
| CONTRACT | TYPE | DATABASE | ENDPOINT | FULLTEXT CATALOG
| FULLTEXT STOPLIST | MESSAGE TYPE | REMOTE SERVICE BINDING
| ROLE | ROUTE | SCHEMA | SERVICE | SYMMETRIC KEY
| XML SCHEMA COLLECTION
}`

So, to use this: My “class_type” is DATABASE, my “entity_name” is the database name, and my “principal name” is my login. My complete statement looks like this:

ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO grrlgeek

I ran that successfully, and there are no more errors.

I can check the owner was changed by running my sys.databases query again.

SELECT NAME, SUSER_SNAME(owner_sid) 
FROM   sys.databases 
WHERE NAME = 'DatabaseName'

Lesson learned: don’t be afraid of new things!