FAQ Topics

All FAQ's

General DBA

General Developer

DBA Performance Tuning

Developer Performance Tuning

Clustering

Error Messages

Write for Us

Share you SQL Server knowledge with others and raise your profile in the community More...

How to set up database diagram in SQL Server Management Studio Express?

Problem:

I am SQL Server Express and after I created my database, I want to set up the database diagram using "new database diagram", however I get error message :

"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."

Solution:

This could be a problem for databases that are inherited from previous version of SQL Server having the database owner as a login that has been changed or not having any permission on this new instance. One resolution is to change that particular database owner to SA and make sure the SQL Server services are using a local or domain account with administrator privileges on the server.

Additionally this error also occurs when the user has initiated the SQL Server Express on top of an MSDE 2000 instance. The user must check for the database compatability level set when it is restored/upgarded on to SQL Express instance. To change the compatbility level of database:

Right-click on the database and select properties from the context menu
Within the Database Properties dialog, click "Options" under the "Select a Page" heading.
There should be a drop down for Compatibility Level with options for SQL Server 7.0 (70), 2000 (80), and 2005 (90), you want the last setting.

If the above actions have no affect on the Diagram creation or you are still getting the above error then refer the following text :

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue was fixed in SQL Server 2005 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 was fixed in SQL Server 2005 SP1.)

By referring to the above steps the user will be able to create the new diagram on the databases that are inherited from previous versions of SQL Server or MSDE instances.