Why database diagrams are not working when domain account is attached to database owner?

Question

I was trying to use Database diagrams is SQL Server and got following error:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

I tried to use this command to solve my problem

ALTER AUTHORIZATION ON DATABASE::mydb TO "domain\username"
GO

but it did not help. When I changed my domain account to sa and ran

ALTER AUTHORIZATION ON DATABASE::mydb TO sa
GO

I got rid of this error. The question is, why domain account is not acceptable for database owner (it seems that only sa annd NT AUTHORITY\SYSTEM are acceptable)?

Answer 1

You don't need to be sa or another sysadmin account to create database diagrams, but I think you may need to do this in order to initially install database diagram support.

If you do this as sa, you should then be able to let domain\username create diagrams. Most of the resources online will tell you that if domain\username is the database owner it should work, but there is a slight difference when you just set a login as the owner of a database and they aren't already a user inside the database: they get mapped to the special dbo account. I seemed to be able to do this in SQL Server 2012, but I used SQL accounts not domain accounts. So I don't know if that's what is causing your issue, but I think I have provided a valid workaround.

Answer 2

A domain account can be a database owner no problem:

In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database.

Generally when I have come across this issue it has been to do with the fact that the database you want to create the diagram in is set to Compatibility Level 80. This will not work. Check your compatibility level and set it to 90 at a minimum. This should resolve your issue.

I hope this helps.