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
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