SQL Server Performance

Database Diagrams Problem...

Discussion in 'SQL Server 2005 General DBA Questions' started by cutiepie, Oct 17, 2005.

  1. cutiepie New Member

    Hi all,
    I am trying to make DATABASE DIAGRAMs....I have restored a database called Tel from a backup file...n in that i have all the tables and procedures..now whenever i m clicking on its DATABASE DIAGRAMS it gives me the error as:

    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.

    ------------------------------
    so,what should i do??any suggestion will be helpful for me...thnks in advance...
    regards..
  2. satya Moderator

    I believe the answer is in the error message, and BOL refers:
    ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals.

    Cannot change the owner of system databases master, model, temp, the resource database, or a database that is used as a distribution database.

    For more information on changing database owner refer to SQL 2005 Books online.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. satya Moderator

    Also<a target="_blank" href=http://download.microsoft.com/download/7/0/1/701ccc32-81b3-476d-9388-3691ac2b2626/ReadmeSQL2005.htm>http://download.microsoft.com/download/7/0/1/701ccc32-81b3-476d-9388-3691ac2b2626/ReadmeSQL2005.htm</a> refers:<i><br />5.8.1 Installation of Database Diagram Support Objects Requires a Valid Logon Account<br />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:<br /><br />Expands the Database Diagrams folder<br /><br /><br />Creates a new diagram<br /><br /><br />Explicitly chooses to install the objects from the context menu<br /><br /><br />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. <br /><br />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.<br /><br /> <br />ALTER AUTHORIZATION ON DATABASE:<img src='/community/emoticons/emotion-2.gif' alt=':d' />atabase_name TO valid_login <br /> <br /></i><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  4. nathaniel New Member

    here's the complete set of commands to get your database diagrams working:


    EXEC sp_dbcmptlevel 'yourDB', '90';
    go
    ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
    go
    use [yourDB]
    go
    EXECUTE AS USER = N'dbo' REVERT
    go

    after that, you should be able to install the diagram options from the context menu and refresh.
  5. cutiepie New Member

    hi,
    thanks a lot for your reply..but i tried the following way..n it worked as well....

    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

    after doing this, I am now able to access the Database Diagrams.

    thanks once again,
    regards.
  6. JusticeLeague New Member

    I tried cutiepie's approach and sadly it did not work for me. Yours, nathaniel is good. Thanks!

    And thank you satya for a great discussion about this issue. Very informative (thumbs up)
  7. envirodat New Member

    quote:Originally posted by cutiepie

    hi,
    thanks a lot for your reply..but i tried the following way..n it worked as well....

    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

    after doing this, I am now able to access the Database Diagrams.

    thanks once again,
    regards.


    I found typin in "sa" as owner also worked and I didn't have to change thelevel to SQL 2000
  8. TomServo New Member

    Thanks, Nathaniel's solution fixed the issue for me.
    Cheers,
    John
  9. shnex New Member

    you're the man ;)
  10. aheg New Member

  11. Log in the Window Autherntication Mode the write(ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO saGO) Lon in to user sa click the diagramthen back to Window Autherntication Mode it's working too.
  12. toalopez New Member

    Hi Cutiepie and Nathaniel, I really apprecicate both of your solutions they are really a great source of knowledge.
    Thank you so much, it really help me out on SQL Server 2005, it does not happened with SQL Server 2008.
    I wish you the best of luck, and keep it up.
    Take Care Guys,
    Angel Lopez
  13. voidtech New Member

    Actually, envirodat helped me with this:

    I found typin in "sa" as owner also worked and I didn't have to change thelevel to SQL 2000.

    Changing to sa was the only thing I had to make. Thanks!

Share This Page