lundi, novembre 28, 2005

Sql Server 2005, database diagram

When I migrate from SQL 2000 to SQL 2005, everything works in the database that was restored, except for the diagrams. I cannot open them and it continues to give me the error message that I do not have a valid owner.
cheking the MSDN article, i find the solution :

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

solution : 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
And
set the database compatibility level to SQL Server 2005 (90)

Aucun commentaire: