Posts
259
Comments
464
Trackbacks
17
FYI: Database Diagrams in SQL Server 2005
I recently restored a SQL Server 2000 database into SQL Server 2005. When I tried to create a database diagram 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. "

After repeated attempts to to assign a valid login user to the database, I found this thread on the MSDN forums:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112297&SiteID=1

I issued the following command and viola! problem solved:

EXEC sp_dbcmptlevel 'EnterYourDatabaseNameHere', '90';

Just thought I'd share what I found...

posted on Monday, February 6, 2006 11:14 AM Print
Comments
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
ANPATI1
2/20/2006 8:18 AM
Thanks a Lot...
That really solves the problem
:( I wasted an entire day with the MSDN documentation.
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
walrusman_c
4/11/2006 6:56 PM
Thanks
It works!!
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Clint
4/14/2006 8:05 PM
Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

Worked for me, anyway.
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Chinedu
5/12/2006 3:47 AM
You don't to know how long I spent tinkering with this problem...If only I found your post much sooner.

Anyone reading this should also visit the MSDN forum identified in the original post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112297&SiteID=1).

Thanks a lot!!
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Harry Chou
5/23/2006 7:08 AM
I did this and broke a lot of scripts and stored procedures. Setting compatibility level to 90 has a lot of implications to how your SQL statements behaves. I just want to make sure if anyone reads this, you should also make sure your current scripts are not affected by the changes. See your 2005 BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Yarun Ghawana
6/30/2006 9:17 PM
Thanks buddy
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
kornolio
9/5/2006 8:17 PM
experienced the same error but my situation was different

i restored my db from a 2005 backup using my domain account in the office. the owner in the 2005 backup was my user account in my home pc. i removed any mapping of the user account in the database and then changed the mapping using the files section from the database properties to the local aspnet user. another option would've been to create an entry in the logins node for the office domain account and grant it db_owner rights in the target database.

hope this helps.
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Jacob Dickinson
4/28/2007 6:21 PM
Yes, thanks much. I particularly appreciate Clint's comment on using Management Studio, and Harry Chou's caution. (I reviewed this "Books Online" topic, but I think I'll get off easily this time!)

I posted a cross-reference to this discussion at the MSDN forum (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112297&SiteID=1).
Gravatar
# re: FYI: Database Diagrams in SQL Server 2000
Karunakaran
12/18/2007 4:31 AM
Could you please let me know how to copy or where the DAtabase diagrame are stored. Also how can i import into visio or other diagaram sw. help needed,

Advance Thanks
M Karunakaran
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Rakesh Patwa
2/2/2008 4:15 AM
Its rocking.

Thanks you so much. I was struggling to solve this problem.

Regards,
Rakesh Patwa.
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
baru
7/29/2008 10:14 AM
i migrated database from SS2K to SS-2K5 using BCP and now I moved database diagrams using DTS Import/Export wizard, but now i cannot view the database diagrams in SS-2K5 database..... any help would be appreciated.
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
its so cold
1/25/2009 7:31 PM
thanks for the code.

I have spent a number of hours trying to enter a valid login.
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
Gary
3/6/2009 12:18 PM
Your the man
Gravatar
# re: FYI: Database Diagrams in SQL Server 2005
V3NOM
5/2/2009 8:18 PM
It doesn't work for me! :(
Comments have been closed on this topic.