none
Problem creating diagrams

    Question

  • I just upgraded an existing sql server 2000 installation with the RTM sql server 2005 (developer). I went to work on a database that I've been working on for a couple of weeks before the upgrade, and wanted to create a database diagram. However, when I click on the "Database Diagrams" tree node, I get this message:

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

    The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.

    Any ideas what's going on?

    brian
    Monday, October 31, 2005 4:00 AM

Answers

  • When the database does not have a valid owner, the database dialog displays the owner as the logged in user.  (This issue will be fixed in 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 will also be fixed in SP1.)



    Tuesday, November 29, 2005 1:15 AM

All replies

  • I haven't been able to reproduce a case where setting the database owner didn't solve the problem.

    I'm suspecting this has something to do with the upgrade process.  When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode. 

    As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram?  (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...)  You can set the database back to SQL Server 2000 compatibility after this experiment.

    Please let me know if this makes any difference.

    Tuesday, November 08, 2005 11:25 PM
  • Hi,

    I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
    I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...

    I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.

    Any hints would be highly appreciated.

    Thanks,
    Florin
    Tuesday, November 15, 2005 12:52 AM
  • I've filed a defect report for this issue.

    Just to make sure I'm understanding you correctly:

    1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
    2) Your database is in SQL Server 2005 compatibility mode
    3) Your database is read/write
    4) Your database is owned by a valid login
    5) You are logged in as an administrator
    6) You are allowing Management Studio to try to install the diagram support objects
    7) You don't get any error messages
    8) You still can't create diagrams

    I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected. 

    As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database? 
    Tuesday, November 15, 2005 1:33 AM
  • I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
     1. Removed all schema's and logins associated with my login in each database
     2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
     3. Gave myself Sysadmin Role in samefolder under Roles
     4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.

    And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.

    I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database  to a User of dbo and default Schema of dbo.

    Hope this helps. - Jeff
    Wednesday, November 23, 2005 4:34 PM
  • Hi,

    Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
    I hope that helps in narrowing down the problem.

    Thank you,
    Florin
    Wednesday, November 23, 2005 6:36 PM
  • When the database does not have a valid owner, the database dialog displays the owner as the logged in user.  (This issue will be fixed in 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 will also be fixed in SP1.)



    Tuesday, November 29, 2005 1:15 AM
  • Thank you very much, Steven,

    For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.

     

    Michelle. 

    Thursday, January 12, 2006 8:09 PM
  • Thanks Steve,

    I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..

    thank u very much once again

     

    Sree

    Friday, February 03, 2006 2:55 PM
  • I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.

    This fixed it;

    1) Switch to compatibility mode 9.

    2) Remove my old diagrams.

    3) Switch back to compatibility mode 8.

    I also ran this command.

    ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]

    Friday, March 31, 2006 8:07 AM
  • I'm having a similar problem with SQL Server Express in Visual Studio.

    The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.

    If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.

    You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?

    Thanks,

    rich

    Wednesday, April 05, 2006 6:25 AM
  • I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1

    rich

    Wednesday, April 05, 2006 4:18 PM
  • Hello,

    I had the same problem without upgrading a database.

    I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.

    Even if you change then the Compatibility level back to SQL Server 2000 its ok.

    Thanks.

    Alex.

     

     

     

     

    Thursday, April 06, 2006 4:30 PM
  • Hi Michelle,

     

    Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.

    Thanks,

    Ron

    E-Mail:  tdi358@yahoo.com

    Sunday, April 09, 2006 2:56 PM
  • I found this post helped me:

    http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true

    When doing this, make sure you are connected to the network if you are using a windows account. 

    Thursday, April 27, 2006 1:11 PM
  • If your db came over from a  SQL 2000 server and it was created by the local admin and not the SA user you will need to change the OWNER under DB PROPERTIES / FILES to SA from machinename/administrator

    Monday, June 19, 2006 4:26 PM
  • I had this problem with the SQL 2000 databases I migrated to SQL 2005 via detach/attach.

    I found that you need to change to Compatibility Mode 90 *and* update the database owner, even if there already is a database owner.  Why, I don't know. 

    To update the database owner, you could change it to a different user, or simply add a space to the end of the current user or change the capitalization of the user (eg "david.hempy" to "david.hempY").  Either of those changes will safely revert back to "david.hempy" after hitting "OK", but it will do the update, which is critical for this solution.  If you don't alter the owner field in some way, it will not update the owner.

    At this point, open the Database Diagrams page, and it will ask you if it can set up Diagrams.  Click OK.

    Interestingly, you can then revert the database back to CM 80, if you have some pressing need.  Apparently it is only the diagram setup process that needs CM 90, not the day-to-day usage.


    Hope this helps,
    -dave

    Friday, December 29, 2006 6:09 PM
  • I have received the same error message and I have tried everything suggested that I could find on the internet.

    I have a closed networked lab. SQL Server 2005 Standard Edition was a new install on one PC we use as the server. No PCs had any previous versions of SQL Server on them. On the other four machines I installed the SQL Server Client software. I am using Windows Authentication for login. My database is in SQL Server 2005 compatibility mode. When a database is created, it is shown to be owned by the login of the person who created it. I still get that error message which is contradictory to what actually exists. The database is owned by a valid user. Users are listed as owner of the database with the string 'LINUXGLOBE\jimbo' and the same name was entered to set up Windows Authentication access.

    On another discussion page, one person said it was because of Windows Authentication mode. SQL Server cannot distinguish a group. I do not know if this is true.

    I have no idea what is happening. Any help would be greatly appreciated.

    Dan

    • Proposed as answer by Aviator007 Friday, December 10, 2010 7:48 PM
    Wednesday, February 07, 2007 9:32 PM
  •  

    I have written an article on this, do check it.

     

    http://venkattechnicalblog.blogspot.com/2010/06/database-diagram-error-in-sql-server.html

    cheers,

    Venkatesan Prabu .J

     

     


    Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/
    • Proposed as answer by Ecolbiz Thursday, January 06, 2011 2:28 PM
    Friday, June 25, 2010 4:04 PM
  • Please take a look on detailed resolution of the issue:

    http://www.a2zmenu.com/MySql/SQ-Database-Diagram-Issue.aspx

     

    • Proposed as answer by Aviator007 Friday, December 10, 2010 7:51 PM
    Friday, December 10, 2010 7:51 PM
  • Thank you!

     

    Very simple solution.

    It works for me.

    Thursday, January 06, 2011 2:30 PM
  • Thanks James Miles. Your command is useful.
    Friday, April 08, 2011 4:25 PM
  • it has 2 way to fix this error


    In SQL Server Management Studio do the following:

    1. Right Click on your database, choose properties
    2. Goto the Options Page
    3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
    4. Goto the Files Page
    5. Enter "sa" in the owner textbox.
    6. Hit OK

    and also you can write this query

    ALTER AUTHORIZATION ON DATABASE::"YOURDATABASENAME" TO "sa"

    • Proposed as answer by Ross Holder Tuesday, August 16, 2011 5:39 PM
    Sunday, June 05, 2011 1:29 PM
  • I have done it

    But my problem (the same with Brian's) did'nt solved

    Please Give my another way to solve it

    Thanks 

    Mohamad-hossein

    Monday, August 13, 2012 5:24 PM