Changing a SQL Server Database Owner

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 }

Read more: LessThanDot

Posted via email from Jasper-net