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
Thank!!
thanks a tonne!!!
Thanks a lot it worked for me
Thanks a lot,
I do it for SQL Server 2012 , and it worked
This was not needed for me :
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
GO
Very big thanks
Thank a lot.
Thank you for the solution ,I was having the same problem and your solution worked for me.
how i can solve my problem
plz solve my problem sql database connection
Trying to fix this for so long, Thanks bro..!
Thanks ………
Error in query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
pls solution this error
Thank you, i just tried it , and it work perfectly.Thx again
Thank you. Although my userID was defined to the server, I still received the error. Then I tried your salutation (setting the user to ‘sa’) and it worked perfectly. Thank you so much!
Thank you !!!
Thank u So Much :) >o< its very Useful For Me Thankssssssssssss :)
Thank You!!!!
Yes, this work for me when i select ‘sa’, thank you very much.
Doesn’t work. Still get the error “”This backend version is not supported to design database …”
Worked Perfect – Thank so Much
would the same solution work for event ID 28005?
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.
I am getting quite few of these errors on a production server after I migrated a database from 2005 SQL to 2012.
Also getting many Event ID 9724
The activated proc ‘[dbo].[dpsvc_Item_data_del_async]’ running on queue ‘fcsd_fcabdatadb.dbo.queue_Item_data_del’ output the following: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’
Any help would be greatly appreciated. thanks