Database diagram support objects cannot be installed

Question

This is pretty confusing. I'm receiving this error whenever I try to add to (or even view) the Database Diagrams node under a Database node within SQL Server Management Studio Express (SQL Server 2008):

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.

Ok, fine. The problem is, according the Management Studio, my login is the owner of the DB. I'm using Windows Authentication when I log into Management Studio, and if I go to the Files page of the Database Properties dialog box, my login is the owner.

So next I figure, ok, maybe I'm not a member of the correct server role to actually be the owner, however, my login is a member of the sysadmin server role, which, according to this MDSN page is a perquisite for owning a database.

So finally I do some googling, and I see people telling me to try executing a statement like this:

EXEC sp_dbcmptlevel 'yourDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N'dbo' REVERT
go

My problem here is that I don't really understand what this code is doing, and no amount of trying different usernames or database names with the above statement (which appears to execute successfully) seems to fix my problem.

Can anyone think of anything I might be missing?

Answer 1

Simply try this.

Alter AUTHORIZATION ON DATABASE::YourDbName TO [sa];

Answer 2

Have you tried this set of steps, it might solve your problem:

In SQL Server Management Studio do the following:

1. Right Click on your database, choose properties
2. Go to the Options Page
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
4. Go to the Files Page
5. Enter "sa" in the owner textbox.
6. Hit OK

Assuming this works and you can add DB diagrams, you can then change the owner back to your Windows user name.

Answer 3

This worked for me

ALTER AUTHORIZATION ON DATABASE ::DATABASE TO sa