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

Recently one of my friend sent me email that he is having some problem with his very small database. We talked for few minutes and we agreed that to further investigation I will need access to the whole database. As the database was very big he dropped it in common location (you can use livemesh or dropbox  or any other similar product) and I picked up from the location.

I was able to install the database successful. He informed me that he has created database diagram so I can easily understand his database tables. As soon as I tried to open the database diagram I faced following error. For a while I could not figure out how to resolve the error.

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 :

Well for a while I attempted few things and nothing worked. After that I carefully read the error and I realized that solution was proposed in the error only. I just have to read it carefully. Here are the steps I did to make this work.

-- Replace YourDatabaseName in following script
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
GO

  • Select your database >> Right Click >> Select Properties
  • 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 should solve your problem.

Please note, 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

120 thoughts on “SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed because this database does not have a valid owner

  1. I have seen this happen when restoring a database, worked for me to change the owner to an account already on the server:

    EXEC sp_changedbowner ‘sa’

  2. Thanks alot man, i have been struggling with this for over 2 days but i had failed to get the solution, keep the good spirit up. Musa K from Uganda

  3. Thanks a lot.
    It also worked for me with one more change. My database was upgraded from SQL Server 2000 to 2005, therefore I changed Compatibility Level in Database Properties\Options from “SQL Server 2000(80)” to “SQL Server 2005(90).

  4. hey I’ve been trying to fix the same issue. I’m doing a project for my client, they already have an existing database and I just need to develop a new module for them. When I copied (detach and attach) the database into my PC and started working on adding new tables, I tried to define relationship using the database diagram but this error occured. Do you have any idea what could happen there? I tried to change the owner to sa but it didn’t work.

  5. This is interesting. I had created a new db in SQL 2012 and had my domain login (domain admin) as the dbo… I saw this error. I followed your advice and changed the owner to NT Authority / System, and now it’s happy.

    When I see multiple postings on a topic and your site comes up, yours is the first link I click. Thank you for being spot-on in your assessments and resolutions!

  6. The top solution didn’t work for me but I used the following statement and it did the job for me….. Good Luck!

    ALTER AUTHORIZATION ON DATABASE::yourDataBase TO “yourLogin”

  7. I tried this and it didn’t work. I tried both sa and NT AUTHORITY\SYSTEM. The message persists, even after rebooting the server. Using SQL Server 2005 on a Win2003 server (no domain). I tried this being signed in as an admin with Windows authentication, and also as sa. Unfortunately, the app we use requires SQL Server, otherwise I would’ve used Postgres or MySQL from the very beginning!

  8. Pingback: 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 « SQL Server Journey with SQL Authority

  9. Thanks a lot,
    I do it for SQL Server 2012 , and it worked

    This was not needed for me :

    ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
    GO

  10. Error in query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

    pls solution this error

  11. Thank you. Although my userID was defined to the server, I still received the error. Then I tried your salutation (setting the user to ‘sa’) and it worked perfectly. Thank you so much!

  12. would the same solution work for event ID 28005?

    An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 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.

    I am getting quite few of these errors on a production server after I migrated a database from 2005 SQL to 2012.

    Also getting many Event ID 9724
    The activated proc ‘[dbo].[dpsvc_Item_data_del_async]‘ running on queue ‘fcsd_fcabdatadb.dbo.queue_Item_data_del’ output the following: ‘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.’

    Any help would be greatly appreciated. thanks

  13. Hello Mr Dave. I tried your solution to this problem but it didn’t work for me. So I checked for more solutions on Google and I came across another solution which solved my problem: http://stackoverflow.com/questions/4103071/sql-server-2008-database-diagram-suppot-objects-cannot-be-installed Now since I am a chap who is ever-hungry for more knowledge, could you kindly shed some light on this? I had installed the Northwind database in SQL Server 2005 and got the error message. According to me, it was the edition of the database engine which was acting as an obstacle to the solution of this problem. I would be glad if you could add extra notes on this scenario in this particular post itself. Thanking you in advance.

  14. Hello.
    Thanks for your contribution, I have a remarque though:
    We can either use the query you posted, or we can go straight forward and fix that with the graphic method you said (step two)…it is the same thing.
    Best regards.

  15. Strange. On SQL Server 2012 I get the same error even after changing the database owner. However, when I get the prompt, I simply click OK, and can then create database diagrams. What gives?

  16. To change all db owners:
    sp_msforeachdb ‘ALTER AUTHORIZATION on DATABASE::[?] TO sa’

    You’ll get the errors on master, etc., but it works on all user DBs

  17. Thanks, Pinal!
    I had this problem with the DB after upgrading to SQL Server from 2008 R2 to 2012.
    One remark: Once I assigned the DB to NT AUTHORITY\SYSTEM, I was able to re-assign it to one of the registered users (for security reasons) – which I was not able to do right away.

  18. Huge thanks from Hawaii! As a new SQL Server hobbyist, I find your site extremely helpful. Thanks for taking the time to do this!

  19. Can I change the DB owner anytime. I have several with a user name and I need to change them all to ‘sa’. Can I do this while people or applications are using the DB?

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