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<br /> ON [ class_type:: ] entity_name<br /> TO { SCHEMA OWNER | principal_name }</p> <p>class_type ::=<br /> {<br /> OBJECT | ASSEMBLY | ASYMMETRIC KEY | CERTIFICATE<br /> | CONTRACT | TYPE | DATABASE | ENDPOINT | FULLTEXT CATALOG<br /> | FULLTEXT STOPLIST | MESSAGE TYPE | REMOTE SERVICE BINDING<br /> | ROLE | ROUTE | SCHEMA | SERVICE | SYMMETRIC KEY<br /> | XML SCHEMA COLLECTION<br /> }

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!