While producing a report for another team today, I wanted to build a database
diagram to see the relationships in the source database. As I was working with a
local copy of the database I decided to build a Database Diagram with Management
Studio, but I received 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.

After checking the database did indeed have a valid owner and
detaching/attaching the database, I was still receiving the error. After a few
minutes I remembered that the original database was on SQL Server 2000.

When restoring a SQL Server 2000 database onto a SQL Server 2005 database,
the internal objects are updated, but the Compatibility Level is left at SQL
Server 2000 (80). Once I’d changed the compatibility of the database to
SQL Server 2005 (90) level the Database Diagram tools began to work.

In Management Studio, you can view/change the Compatibility level of your
database in the the Options group of the Database Properties:

You can check the compatibility level of your databases with:

SELECT name, compatibility_level FROM sys.databases

And you can upgrade a database compatibility level with this code:

USE [master]
GO
DECLARE @dbname sysname
SET @dbname = N’your database name‘
IF EXISTS (SELECT * FROM sys.databases WHERE compatibility_level != 90 AND NAME
= @dbname)
EXEC dbo.sp_dbcmptlevel @dbname=@dbname, @new_cmptlevel=90
GO

Tags: Management Studio, SQL Server, Tools