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 scriptALTER 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)