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

Tags:

About kiranpatils

As a passionate programmer I am enjoying in this field for more than 6 years, currently leading a development team. This team is responsible for keeping high up-time and low response time of application, debugging critical performance related issues, analyzing live issue as a high priority, application version upgrade and above all developing new features on top of Sitecore. Many a times I get involved in client conversations as well for new requirement or wherever some technical or functional guidance is required. Other than that, I do enjoy working in a team and building the overall strength of team by effective knowledge sharing and guidance. Spending free time with family and friends, listening music, reading books, traveling and exploring the nature, keeps me recharged! Recognized as a Microsoft Certified Technology Specialist - Web and Windows, and a Microsoft Certified Professional Developer - Web. Enjoys helping others for solving technical issues. Writes regular posts at : http://kiranpatils.wordpress.com/ and http://sitecorebasics.wordpress.com/

12 responses to “Cannot Create Database Diagram with SQL Server 2005”

  1. Sue Massey says :

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

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

  3. Agha Usman says :

    Yep … did my job great work buddy

  4. Dhwani says :

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

    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.

  6. Brij says :

    Thanks its really working :)

  7. Seri says :

    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

  8. Experts Comment says :

    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 328 other followers

%d bloggers like this: