Geeks With Blogs

Tim Huffam Dotting the I and crossing the T of I.T.

When trying to add a diagram to an SQL Server 2005 db I got the following 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.

I tried the suggestion mentioned in the message but that didnt work.  Also found that SQL Server Management Studio does not like you trying to change various permissions - it only works certain ways (go figure).

Anyway, eventually found this post with the solution (thanks very much to cutiepie) being:

In SQL Server Management Studio do the following:

  1. Right Click on your database, choose properties
  2. Goto the Options Page
  3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"

 

Posted on Wednesday, July 5, 2006 7:36 PM SQL Server , ASP.NET | Back to top


Comments on this post: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Great job done, I was having the same problem
Thanks a lot !
Left by Ram Shankar Yadav on Jul 06, 2006 10:50 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Your solution only works in cases where compatibility level is set to something other than the default. There are in fact a variety of cases where this error message is the result. That's why it's so insanely annoying and misleading. The most common case is still that the user creating the database does not have a specific login account but rather is part of a group with login rights.
Left by MonsoonDawn on Jul 07, 2006 11:50 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Actually, once you set the compatibility level to SQL 2005, you should install the diagram components (by click on the diagram node) and then set the compatibility back to your default. This way you won't run the risk of having some of your old scripts generate errors. I paid my price to learn this trick.
Left by Harry Chou on Jul 08, 2006 1:40 AM

# Solution to: "Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner."
Requesting Gravatar...
I found the answer in this post via Larkware. There are some other alternatives over here. I came accross
Left by Community Blogs on Jul 11, 2006 2:29 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
awesome, wondered why on earth i couldnt change the database owner (as the error suggests)
didn't even think to check the compatibilty level since i upgraded from a 2000db.
Left by agrath on Aug 02, 2006 8:05 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thank you for saving newbies to SQL Server 2005 like me. Appreciate your help a lot. Happy programming!
Left by Yee Chee Leong on Aug 03, 2006 8:18 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
God Bless
Left by Julius on Aug 16, 2006 9:29 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
I tried the above mentioned solution but still was not working and displaying the same error message - if you got the same error as me then just try the follwing lines in SQL Query:

EXEC sp_dbcmptlevel 'dbname', '90';

ALTER AUTHORIZATION ON DATABASE::dbname TO valid_login
Left by Julius on Aug 16, 2006 9:39 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thank you a milion x a milion
Left by DrHOffman on Sep 04, 2006 10:33 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
I got the same message today. I've also tried all the suggested solutions with no luck. In fact the exact same database works on my local machine, but as soon as i script it out and deploy it it complains... even with the same db owner. Very confusing
Left by tups on Oct 10, 2006 9:30 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thanks a lot. Saved me heaps of time
Left by Carmy on Oct 23, 2006 9:19 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
it's correct to set the compatibility level to 90 (slq2005) but you'll also ned to set a valid db owner, right click on the database, file tab and set SA as db owner (or a valid mssql user)

bye!
Left by Simone on Nov 02, 2006 8:07 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thanks so much!!!
Left by horseperson on Jan 14, 2007 6:52 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thanks Julius,
Your solution works for me.

Left by Mirko on Jan 22, 2007 8:42 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thanks for posting this, worked for me.
Left by Dana on Jan 27, 2007 4:18 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Thank you so much to everyone!! This solved a very annoying problem. A huge middle finger to Microsoft!!
Left by Sean vd Walt on Feb 01, 2007 8:11 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Both ways did not work the second way gave me an error when running the query. The first way gave me the same error when I switched the Compatability to 2005.


Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'valid_login', because it does not exist or you do not have permission.
Left by Grahame C on Mar 02, 2007 11:26 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner.
Requesting Gravatar...
Strike that, ... I missed the post about adding the 'sa' as a owner in the file tab under the database. Once I did that it worked with out error. Sorry about the extra posts.
Left by Grahame C on Mar 02, 2007 11:32 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thanks this article that came up as #1 solution on Google.
Left by Shyam on Jul 19, 2007 8:15 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thanks so much! I simply had to execute the following:

EXEC sp_dbcmptlevel 'dbname', '90';

ALTER AUTHORIZATION ON DATABASE::dbname TO valid_login

where 'dbname' = My Database Name
and
valid_login = sa (or any other user name you have specified)
Left by Robert Green on Nov 22, 2007 1:10 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Hey,

I tried all the other solutions on the web and they didnt work, but yours fixed it!

Thanks a million. :)
Left by Chris on Dec 03, 2007 11:30 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
God bless you , Well-written article.
Left by chetan on Jan 21, 2008 5:06 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thanks a lot
Left by surekha praveen on Feb 11, 2008 8:52 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thank you. I have fixed my probleme
Left by vavavivi on Mar 13, 2008 7:58 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thank u so much...
by this great post, i am able to fix my problem.
Left by Sangram on Mar 13, 2008 11:00 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
I tried the solution which author has given. But that didn't worked.
I did the following, and now it is working well. Thanks lot. ;)

EXEC sp_dbcmptlevel 'dbname', '90';
ALTER AUTHORIZATION ON DATABASE::dbname TO sa

I'm authenticated using windows login. Even then also "sa" worked well for me.

Thanks once again.
Left by Ashok kumar on Apr 10, 2008 9:48 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thanks a lot i was thinking of installing SQL Server again. Now my problem is solved
Left by Rizwan on May 07, 2008 11:54 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
THANK YOU. This problem was annoying me more than a month. I solved it right after read your solution.

GREAT!!!!
I AM VERY GRATEFUL TO YOU.
Left by Rəşad on May 14, 2008 11:16 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
thanx a lot....
Left by baljeet singh on May 16, 2008 3:26 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
thanks dude,
Left by darklord on Jun 21, 2008 11:47 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
thanks alot, it saved my time to seek further
Left by dude on Aug 20, 2008 10:30 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
Thanks -- very helpful.
Left by SMC on Dec 06, 2008 11:37 AM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
EXEC sp_dbcmptlevel 'dbname', '90';
ALTER AUTHORIZATION ON DATABASE::dbname TO sa

Hurray it's working. Thanks whoever posted this query........................
Left by Shajeeb S on Jan 01, 2009 8:03 PM

# re: Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does no
Requesting Gravatar...
This :

EXEC sp_dbcmptlevel 'dbname', '90';
ALTER AUTHORIZATION ON DATABASE::dbname TO sa

Worked great!

Thanks!

Phil
Left by Philippe B on Jul 17, 2009 8:33 AM

# Man
Requesting Gravatar...
Thank you man. i was need of this thing.

EXEC sp_dbcmptlevel 'dbname', '90';
ALTER AUTHORIZATION ON DATABASE::dbname TO sa

Thanks once again.
Left by Irfan Ahmad (Isfan) on Sep 26, 2009 2:42 AM

Your comment:
 (will show your gravatar)


Copyright © Tim Huffam | Powered by: GeeksWithBlogs.net | Join free