Skip to content
January 26, 2008 / kiranpatils

Cannot Create Database Diagram with SQL Server 2005

Cannot Create Database Diagram with SQL Server 2005

Today one of my associate has faced one problem: He wants to create Database Diagram in SQL SERVER Management Studio. But he is getting the error like shown as below.

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

He asked me to find the solution and I found it. So I am putting it here. So you don’t have to struggle for it.

SOLUTION:

  1. Just copy below SP and copy it to Management studio

EXEC sp_dbcmptlevel ‘<Dbse name>’, ’90’;

go

ALTER AUTHORIZATION ON DATABASE:: <Dbse name>TO<USER Name>

go

use [<Dbse name>]

go

EXECUTE AS USER = N‘dbo’ REVERT

Go

  1. Now just change <Dbse name> to your Database name for which you are facing problem and <USER Name> with your username of SQL.
  2. Now Try it!!! And Enjoy

Happy Database Diagramming

-Kiran

About these ads

12 Comments

Leave a Comment
  1. Sue Massey / Jan 26 2008 5:52 am

    I found your site on google blog search and read a few of your other posts. Keep up the good work. Just added your RSS feed to my feed reader. Look forward to reading more from you.

    – Sue.

  2. SillyFools / Aug 15 2008 4:02 am

    Thanks a lot.
    this solution help me very very much..

  3. Agha Usman / Sep 29 2008 7:32 pm

    Yep … did my job great work buddy

  4. Dhwani / Apr 17 2009 7:27 am

    Below is the way for how to do it for windows authenticated user:

    EXEC sp_dbcmptlevel ”, ’90′;
    go
    ALTER AUTHORIZATION ON DATABASE::[] TO [MANOHAR\Administrator]
    go
    use []
    go
    EXECUTE AS USER = N’dbo’ REVERT
    Go

  5. Amit Kumar / Mar 6 2010 6:46 am

    Hi,

    Sometimes the above script may not work properly in case of mixed authentication.

    We may need to remove the owner of database by going in
    Security -> Logins -> Select the user and double click -> Goto User Mapping.

    Now right click on your database name and goto Properties -> Files

    Set the Owner here and save settings.

    This will definately work for you in SQL 2008 as well.

    • SK / Sep 25 2010 12:55 pm

      Worked !!! Thank you…

  6. Brij / Mar 16 2010 7:47 pm

    Thanks its really working :)

  7. Seri / Mar 18 2010 1:33 am

    Hi ALL,

    I tried it :
    EXEC sp_dbcmptlevel ‘’, ‘90′;

    go

    ALTER AUTHORIZATION ON DATABASE:: TO ““

    go

    use []

    go

    EXECUTE AS USER = N‘dbo’ REVERT

    Go
    =======================

    However, I get the error message below :

    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ‘‘’.
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘AUTHORIZATION’.
    Msg 911, Level 16, State 1, Line 2
    Could not locate entry in sysdatabases for database ”. No entry found with that name. Make sure that the name is entered correctly.
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘AS’.

    ——————————————————

    I cut and paste the provided Solutions on A new Query and execute it.

    Sorry I am a new bie, am i doing something wroing?
    Would you please correct me?

    Thank you

    • kiranpatils / Apr 12 2010 5:45 am

      You can replace “ with ” and give a try!

  8. venkatesan prabu / Jun 25 2010 4:10 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

    • kiranpatils / Jul 20 2010 4:45 pm

      Thanks Venkatesan Prabu — Good article! Keep it up :-)

  9. Experts Comment / Oct 7 2010 7:04 pm

    The reason for this error is because the database backup was taken from different machine.To fix this issue, we need to add a valid user account as owner for this database… refer to this link for more details

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 415 other followers

%d bloggers like this: