SQL 2005 - Database diagram

This error always get me and it reminds me of something Brian has said on one of his agile presentations: First time it occurs, fix it. Second time is coincidence. Third time, refactor it (or something like that...) This is like the 4th time I get this error, so I'm writting it in here so that it get indexed and will help me remember how to fix it.

If you tried to restore a SQL 2000 database backup into a SQL 2005. You will find out that the database diagram does not upgrade easily. If after restoring the database, you click on the database diagrams and get this 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.

There are 3 solutions to this problem:
a. if the default compatibility is other that SQL 2005(90), then set the compatibility level back to sql 2005. This is done by right click on the database then select properties --> Options.



b. Execute the following command on the query analyzer window

ALTER AUTHORIZATION ON DATABASE::your_dbname_here TO sa

then after running this command, you can go ahead and add a new database diagram.

c. If you right-click the database you are interested on. Then select properties, select Files you will see that there is no owner specified on the text box for this database. Enter a valid userID (i.e. sa) in the box. Then the database diagram will start working.