Error: Database diagram support objects cannot be installed because this database does not have a valid owner
If you have been using the database diagrams from SQL Server 2000 onwards and when it is upgraded to SQL 2005 you might get the following error:
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.
------------------------------
In order to fix it follow the trail below, before that I would like to explain about AUTHORIZATION statement and extract features you need to database diagram support objects within SQL Server 2005.
First issue to note if you have restored the database from SQL 2000 to 2005 then ensure you are using database compatibility 90 and not 80, this is where firstly you would get such errors.
As per the BOL: ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals.
For the download of Database Diagram Support Objects refer to ThisLink as it states:
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
Similarly you can use SSMS to perform:
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. Hit OK