none
Database diagram support objects cannot be installed...

    Question

  • I can't make a diagram on our server(Win2003 x64,SQL 2005 Enterprise x64 sp2 3054).

    I have tried to login as an admin and create a new db, but get the same message -

    'Database diagram support objects cannot be installed because this database does not have a valid owner. To..'

    Also tried to make a diagram from the ReportServer db.

     

    Any suggestions?

    Thursday, May 24, 2007 7:37 AM

Answers

  • Hi,
    The real problem is that the default owner(dbo) doesn't have a login mapped to it at all.As I tried to map the sa login to the database owner  I received another error stating "User,group, or role 'dbo' already exists...".However if you try this code it will actually works :

    EXEC sp_dbcmptlevel 'yourDB', '90';
    go
    ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
    go
    use [yourDB]
    go
    EXECUTE AS USER = N'dbo' REVERT
    go
    Saturday, November 22, 2008 6:18 PM

All Replies

  • Two things to check.

     

    1. please make sure that db has a owner (as the error suggests).

    2. please check your compatibility level.

    Friday, May 25, 2007 5:50 PM
  • Thanks Meher for your reply.

     

    1. Both of the databases I tried has a owner.

    2. I'm pretty sure the compatibility level is 90 (SQL 2005) since one is created during installation(ReportServer) and the other is created as a new db in SQL Management Studio. (To be sure I will check next week)

     

    Any other suggestions?

     

    Friday, May 25, 2007 6:33 PM
  • Are you still getting the error message?. As you have mentioned check the compatibility level once again and if you are still getting the message we can look a bit further.

     

     

    Friday, May 25, 2007 9:22 PM
  • The compatibility level is 90(SQL Server 2005).

     

    Monday, May 28, 2007 7:05 AM
  • what role do you belong to?. pls check

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/6fdecefb-7bd3-4203-a58c-36d46ee628bf.htm

    Monday, May 28, 2007 1:27 PM
  • db_owner  
    Tuesday, May 29, 2007 11:37 AM
  • Hi olof,

     

    can you please go through the steps outlined in section 4.8.1 in http://support.microsoft.com/?id=910228

    As per the section in the article, looks like you might have to install the diagram support.

     

    To create database diagrams, change the database compatibility level to 2005, install database diagram support, and then   return the database to the desired database compatibility level.

     

      MVPs, please correct me if I am missing something.

     

    Thanks

     

    Meher

    Tuesday, May 29, 2007 4:25 PM
  • one other thing I forgot to mention is that the installation of support objects can fail if the database

    has been attached or restored from another instance of SQL Server. In such a case there is a

    possibility that the database owner name stored in the database may not be a valid logon for the

    instance of the SQL Server on to which the database is restored to (or attached to).

     

    I would suggest if thats the case then you can use the Alter statement to change the owner to a valid logon.

     

    ALTER AUTHORIZATION ON DATABASE::your dbname TO yourdesiredlogin

    (please see BOL for more details on ALTER AUTHORIZATION).

     

    I would suggest doing through T-SQL than UI and see if it solves your issue.

     

    Thanks

     

    Meher


     

     

    Tuesday, May 29, 2007 4:35 PM
  • Using T-sql instead of ui did the trick for me.

    thanks Meher

    Monday, October 01, 2007 11:24 PM
  • Just wanted to add that I was able to connect and install the diagram support objects by using Visual Studio rather than SQL Server Management Studio -- without having to do the ALTER AUTHORIZATION statement.  Steps below:

     

    • Opened Visual Studio 2005
    • Added the Server and database to the Data Connections on the Server Explorer tab.
    • Click Database Diagrams to expand
    • Received message box: This server does not have one or more of the databse objects required to use database diagramming.  Do you wish to create them?  Clicked YES
    • Diagrams showed up - Yay!
    Friday, September 05, 2008 4:11 PM
  • The same bug also exists in SQL 2008 in it's unchanged form.

    Friday, October 03, 2008 2:05 PM
  • Hi,
    The real problem is that the default owner(dbo) doesn't have a login mapped to it at all.As I tried to map the sa login to the database owner  I received another error stating "User,group, or role 'dbo' already exists...".However if you try this code it will actually works :

    EXEC sp_dbcmptlevel 'yourDB', '90';
    go
    ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
    go
    use [yourDB]
    go
    EXECUTE AS USER = N'dbo' REVERT
    go
    Saturday, November 22, 2008 6:18 PM
  • Thanks for this post.

     

    This has saved me a lot of time and worked first time.

    Monday, December 01, 2008 9:04 AM
  • I ran the query and the following was returned:
    Msg 15110, Level 16, State 1, Line 1
    The proposed new database owner is already a user or aliased in the database.
    Msg 15517, Level 16, State 1, Line 1
    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.


    sandeep
    Tuesday, March 24, 2009 7:55 AM
  • I tried this technique using Visual Studio 2008 Team System Developer Edition SP1 and SQL Server 2008 Developer Edition SP1 and got an error:

    The message box that pops up after attempting to expand the Database Diagrams node of the tree says:

    This database does not have a valid dob user or you do not have permissions to impersonate the dbo user, so database
    diagramming is not available.  Do you want to make yourself the dbo of this database in order to use database diagramming?
    (Yes)/(No)/(Help)

    After clicking Yes to the first message, you get a second message:

    This server does not have one of more of the database objects required to use database diagramming.  Do you wish to create them?
    (Yes)/(No)/(Help)

    When I click on the Yes button, the following error message pops up:

    Could not obtain information about Windows NT group/user '<mydomain>/<myuserid>', error code 0x5.

    What a pity this is still a problem in SQL Server 2008 after all these many months for Microsoft to fix it. :-(
    Fred Morrison
    Wednesday, April 15, 2009 6:56 PM
  • If the issue is to only the diagram then copy the table script and create a test database. execute the script in the test database
    and create the diagram.This worked for me.
    Thursday, May 21, 2009 9:05 PM
  • Hi,
    The real problem is that the default owner(dbo) doesn't have a login mapped to it at all.As I tried to map the sa login to the database owner  I received another error stating "User,group, or role 'dbo' already exists...".However if you try this code it will actually works :

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

    This work wery well!!
    thx

    Maxidag
    Monday, July 20, 2009 7:53 AM
  • It worked in sql server 2008, after setting "sa" as  Owner.

    Right click database in  management studio then click properties.
    select "files" page
    then set owner as "sa"

    Thursday, January 21, 2010 10:24 PM
  • thankssssss sooooooooooo muchhhhhhhhhhhhh
    Thursday, February 04, 2010 9:06 PM
  • Thanks.... It worked like a charm !!...
    Sunday, April 04, 2010 10:44 PM
  • Worked brilliantly thanks
    Tuesday, June 29, 2010 1:43 PM
  • This worked for me!

     

    But it doesn't autogenerate the ERDs?

     

    Alagu

    Wednesday, June 30, 2010 4:33 AM
  • I encountered this problem as well and setting the owner to "sa" is perfect.

    Thank you so much!

    Wednesday, August 11, 2010 1:14 AM
  • Ir worked for me... Thankyou very much

    Monday, August 30, 2010 8:39 PM
  • Thank you - that worked perfectly.

    Gary.

    Wednesday, September 01, 2010 5:26 PM
  • As simple as changing it to sa worked as a charm. Thanks.
    Monday, December 06, 2010 5:16 PM
  • Thanks alot! - this worked for me !
    Thursday, January 13, 2011 12:30 PM
  •  

    exelente 100% esta es la mejor respuesta

     

    • Opened Visual Studio 2005
    • Added the Server and database to the Data Connections on the Server Explorer tab.
    • Click Database Diagrams to expand
    • Received message box: This server does not have one or more of the databse objects required to use database diagramming.  Do you wish to create them?  Clicked YES
    • Diagrams showed up - Yay!
    Wednesday, February 02, 2011 1:36 PM
  • Thanks Corvin, It works for me
    Live
    Sunday, May 29, 2011 12:37 PM
  • It worked in sql server 2008, after setting "sa" as  Owner.

    Right click database in  management studio then click properties.
    select "files" page
    then set owner as "sa"


    Of all the solutions here - and I tried them all - this was the only one that worked.

    Thanks!

    Was trying on a local install of 2008 R2, on a database I had created a little while earlier.

    Tuesday, December 20, 2011 9:01 PM
  • I had the same problem as you mentioned and I was able to do the following steps to solve the owner issue in MS SQL Management Studio.
    Please make sure that you don't select a user name (in step 4 in the figure) that is already Admin for the selected database.

    Steps:

    1. Right-Click on [Database name] -> Properties.
    2. Database Properties - [db name] -> Files ->click on [...] button in front of owner.
    3. Select Database Owner -> [Browse...]
    4. Browse for Objects -> Select one non-admin username

    5. Hit OK buttons in all windows.
    Its all done.


    Wednesday, April 18, 2012 10:35 AM
  • Thankz workz fine..............::::::::::::::::
    Thursday, May 03, 2012 7:46 AM
  • Thanks very much!!!!!!!!!!!!
    Monday, June 11, 2012 6:55 AM
  • Thank you Khalid Mehmood Chitrali very much!
    Saturday, August 25, 2012 4:18 PM
  • thanks janajey it works grate !!!!!!!!
    Monday, September 17, 2012 4:10 AM
  • It worked in sql server 2008, after setting "sa" as  Owner.

    Right click database in  management studio then click properties.
    select "files" page
    then set owner as "sa"

    Thanks. This saved my life!!
    14 hours 21 minutes ago