Database diagram issues after SQL Server upgrade
Have you planned your databases upgrade from SQL Server 2000 version to 2005 or 2008?
Have you tested the above upgrade plan?
How many times you have been involved in the number of databases upgrade within your platform?
Many questions to answer when you think about upgrade, in this regard to addup to the Upgrade-FAQ_Blurb I have been involved in an upgrade process that involves a SQL Server 2000 instance to a SQL 2005 and 2008 instances. This is to require to test the application completely for 2005 & 2008 features compatibility. All the processes for upgrade has been completed successfuly and we hit the problem when one of the developer tried to create a database diagram! However, when the user clicks on the "Database Diagrams" tree node the following error message is displayed:
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.
------------------------------
BUTTONS:
OK
------------------------------
ALTER AUTHORIZATION is the new statement introduced in SQL Server 2005 version, that will be used for ownership of the schema-contained entities of type "object" can be transferred, such as: tables, views, functions, procedures, queues, and synonyms. Whereas few other entities such as: linked servers, statistics, constraints, rules, defaults, triggers, Service Broker queues, credentials, partition functions, partition schemes, database master keys, service master key, and event notifications cannot be transferred. In addition to that within SQL Server 2008 the ownership of securable classes cannot be transferred: server, login, user, application role, and column.
To continue working on the above error message make sure you have assigned the appropriate rights as defined below:
EXEC sp_dbcmptlevel '<UpgradedDB>', '90';
go
ALTER AUTHORIZATION ON DATABASE::UpgradedDB TO "TheLogin"
go
use [UpgradedDB]
go
EXECUTE AS USER = N'dbo' REVERT
go
After checkout of the above process the error still persisting, so I had a doubt on upgrade process that has been carried out. Also tried to create the diagram by setting the upgrded database compatibility to 80 and created the user again on the database with no success. Also followed the workaround to remove all the schemas and logins that are associate with this user's login from each database, also remove all the associated mappings. As a matter of fact the requirement was urgent and it has to be finished soon so they granted the user with SysAdmin role!
But I was more keen on why the error is generated and as per the Microsoft documentation it confirms the issue of database does not have a valid owner has been fixed in Service Pack 1 for SQL Server 2005. 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. Due to the reason that 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. To resolve this problem the above TSQL needs to be used to explicitly setting the owner to a valid principal on the server, which should solve the problem.
SImilar to the above the Management Studio User Interface problem also occurs due to the install of 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 should solve the problem, the above bug has been fixed with SP1 as specified.
The issue was due to the mismatch of login name that is used in SQL Server 2000 instance is different to SQL Server 2005 & 2008 which are the upgraded databases, well the spelling was right but not the CASE. As the SQL 2000 instance has been installed (earlier) with accent-sensitive & case-sensitive that has been missed while installing the SQL 2005 & 2008 instances. So this is another point for every DBA to make sure that all the confirmation during pre-setup and post-setup must be documented to ensure these smaller options are not missed.