Database Diagram problems when upgraded from SQL 2000 to 2005
When you have tested the application for SQL Server 2005 comtability and upgrade process then your UPGRADE task will be easy to manage. But if you have used the Diagrams within SQL Server during the previous version, then there is a problem and here is how you can resolve.
When you try to access diagrams in SQL 2005 then following error will be displayed:
TITLE: Microsoft SQL Server Management Studio
------------------------------
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.
------------------------------------------------------
Pretty straight forward error isn't it!
First option is to check whether that database has a valid owner as when you upgrade from SQL 2000 to 2005 there might be a chance of missing the logins transfer too. As a best practice you can either select SA as a database owner or choose a valid login that is present on that SQL Server 2005 instance.
Further one of the technet article refers that:
5.8.1 Installation of Database Diagram Support Objects Requires a Valid Logon Account
In SQL Server 2005, database diagram support objects are installed on a database in which the support objects have not yet been installed if a member of the db_owner fixed database role performs one of the following operations:
Expands the Database Diagrams folder
Creates a new diagram
Explicitly chooses to install the objects from the context menu
The installation of these support objects can fail on a database that has been attached or restored from another instance of SQL Server. This can occur when the database owner name (stored in the database) is not a valid logon for the instance of SQL Server the database is being attached or restored to.
Use the following Transact-SQL expression to change the database owner to a valid logon for the instance of SQL Server. Then, retry the database diagram operation.
ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login
Once you have completed the relevant ALTER AUTHORIZATION tasks then ensure to install the diagram options from the context menu of Management Studio.
There is also another 'cheeky' way to accomplish this task, but it is better to follow the above Technet reference that will ensure to staple the problem forever.
The other method is:
In SQL Server Management Studio do the following:
1. Right Click on your database, choose properties
2. Goto the Options Page
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
4. Goto the Files Page
5. Enter "sa" in the owner textbox.
6. Click OK button