SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed

Recently, one of my friends sent me email that he is having some problem with his very small database. We talked for a 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 a common location. Let us learn about error Database diagram support objects cannot be installed because this database does not have a valid owner.

I was able to install the database successfully. He informed me that he has created a 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 a 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 (https://blog.sqlauthority.com)