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)
184 Comments. Leave new
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’
Thanks my issue has been resolved
Thanks @Saad
THANKS SO MUCH!
Thank you the solution worked
Thank you! I was wrestling with this!
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
Thank uuuuuuu
Thanks.
thanks.
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 PropertiesOptions from “SQL Server 2000(80)” to “SQL Server 2005(90).
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.
Thank you !!
Thank you so much! excellent explanation.
thanks man
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!
Thank you so much this is working for me.
Thank you!!!!
Excellent!!!
Thanks ………..
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”
thanks very very very so much!!!!!