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
Thanx a lot
you are the best, thank you so much
Thankyou very much
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: 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.
i love you man !!!thanks
simple!
solved!
thanks!
Hari
Great Buddy thanks..!it’s Great Work
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.
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?
Worked great. This was causing our backups to fail. Thank you.
thanks for the solution… it worked for me
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
tnx maan
thanks…
Thank you very much.
Precise and very helpful as always. Thank you very much!
Thanks!!!!!!
Thank you so much!!!!
Thanks a lot! this worked nicely!
many thanks!!