Friday 2 April 2010

Problem creating ER diagrams

Question: 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.

Answer:

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)