SQL SERVER – ERROR: FIX using Compatibility Level – Database diagram support objects cannot be installed because this database does not have a valid owner – Part 2

Earlier I wrote a blog post about how to resolve the error with database diagram. Today I faced the same error when I was dealing with a database which is upgraded from SQL Server 2005 to SQL Server 2008 R2. When I was searching for the solution online I ended up on my own earlier solution SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed because this database does not have a valid owner.

I really found it interesting that I ended up on my own solution. However, the solution to the problem this time was a bit different. Let us see how we can resolve the same.

Error:
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.

Workaround / Fix / Solution :

Follow the steps listed below and it should for sure solve your problem. (NOTE: Please try this for the databases upgraded from previous version. For everybody else you should just follow the steps mentioned here.)

  • Select your database >> Right Click >> Select Properties
  • Go to the Options
  • In the Dropdown at right labeled “Compatibility Level” choose “SQL Server 2005(90)”
  • Select FILE in left side of page
  • In the OWNER box, select button which has three dots (…) in it
  • Now select user ‘sa’ or NT AUTHORITY\SYSTEM and click OK.

This will solve your problem. However, there is one very important note you must consider. When you change any database owner, there are always security related implications. I suggest you check your security policies before changing authorization. I did this to quickly solve my problem on my development server. If you are on production server, you may open yourself to potential security compromise.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – ERROR: FIX using Compatibility Level – Database diagram support objects cannot be installed because this database does not have a valid owner – Part 2

  1. Good Stuff – While I was trying to build my Joe2Pros DB Diagram, I did the exact same thing – More ppl will benefit now from your articles :)

  2. Thank you for the post sir. I read the part 1 and part 2.

    I understood the part 1 but for part 2 I have a question, Please correct me if I’m wrong, as part 2 is applicable for the database which is upgraded from previous verson and to fix this issue I need to change the compatibility level to previous verson then Can I say to fix this issue I have to downgrade my database compatibility? or after following the above steps I need to set the database compatibility level again ( in this case from 90 to 100).

    • Hi Sandip,

      You are very correct. After fixing the problem one should bring the compatibility to 100 however, make sure nothing else is breaking in your code or else fix that first and then move it.

  3. Thank you
    but
    I have a problem its
    SQL SERVER R2
    column tel type int null
    When the data sent through vb.net telphone.text
    d.sav_dele_up(“insert into conver values(” + Me.telphone.Text + “,’” + Me.student_name.Text + “‘,’” + Me.semester.Text + “‘,’” + datedtime + “‘,’” + school.Text + “‘,’” + Me.resulte.Text + “‘)”)

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