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.
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 (http://blog.sqlauthority.com)