Twice is two weeks SQL 2005 has reported the following error when I've clicked on the database diagrams node on certain databases;
"Database diagram support objects cannot be installed because this database does not have a valid owner".
The databases in question were restored onto my local SQL from backups on other machines and servers. I'd restore the database, set the owner using ALTER AUTHORIZATION, and then try to create a database diagram, only to be presented with the above error.
The symptoms actually mask the underlying problem - the database's compatibility mode. On both occasions the compatibility level was set to SQL 2000 (80) for the restored databases and it should be set to SQL 2005 (90). To resolve;
- Open the properties for the affected database
- Go to the options section
- Change the compatibility level to SQL 2005 (90)