Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this 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.

Section 5.8.1 of the SQL 2005 Readme file says:
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.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

sql