SQL Server Performance Forum – Threads Archive

Database Diagrams Problem…

Hi all,
I am trying to make DATABASE DIAGRAMs….I have restored a database called Tel from a backup file…n in that i have all the tables and procedures..now whenever i m clicking on its DATABASE DIAGRAMS it gives me the error as: TITLE: Microsoft SQL Server Management Studio
—————————— 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. ——————————
so,what should i do??any suggestion will be helpful for me…thnks in advance…
regards..

---

I believe the answer is in the error message, and BOL refers:
ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals. Cannot change the owner of system databases master, model, temp, the resource database, or a database that is used as a distribution database.
For more information on changing database owner refer to SQL 2005 Books online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Also<a target="_blank" href=http://download.microsoft.com/download/7/0/1/701ccc32-81b3-476d-9388-3691ac2b2626/ReadmeSQL2005.htm>http://download.microsoft.com/download/7/0/1/701ccc32-81b3-476d-9388-3691ac2b2626/ReadmeSQL2005.htm</a> refers:<i><br />5.8.1 Installation of Database Diagram Support Objects Requires a Valid Logon Account<br />In SQL Server 2005, database diagram support objects are installed on a database in which the support objects have not yet been installed if a member of the db_owner fixed database role performs one of the following operations:<br /><br />Expands the Database Diagrams folder<br /><br /><br />Creates a new diagram<br /><br /><br />Explicitly chooses to install the objects from the context menu<br /><br /><br />The installation of these support objects can fail on a database that has been attached or restored from another instance of SQL Server. This can occur when the database owner name (stored in the database) is not a valid logon for the instance of SQL Server the database is being attached or restored to. <br /><br />Use the following Transact-SQL expression to change the database owner to a valid logon for the instance of SQL Server. Then, retry the database diagram operation.<br /><br /> <br />ALTER AUTHORIZATION ON DATABASE:<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atabase_name TO valid_login <br /> <br /></i><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>

---

here’s the complete set of commands to get your database diagrams working:
EXEC sp_dbcmptlevel ‘yourDB’, ’90’;
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N’dbo’ REVERT
go after that, you should be able to install the diagram options from the context menu and refresh.

---

hi,
thanks a lot for your reply..but i tried the following way..n it worked as well…. 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)"
4. Goto the Files Page
5. Enter "sa" in the owner textbox.
6. Hit OK after doing this, I am now able to access the Database Diagrams. thanks once again,
regards.

---

I tried cutiepie’s approach and sadly it did not work for me. Yours, nathaniel is good. Thanks! And thank you satya for a great discussion about this issue. Very informative (thumbs up)

---

I found typin in "sa" as owner also worked and I didn’t have to change thelevel to SQL 2000

---

Thanks, Nathaniel’s solution fixed the issue for me.
Cheers,
John

---

you’re the man

---

Thanks cutiepie.
That worked great.

---

Log in the Window Autherntication Mode the write(ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO saGO) Lon in to user sa click the diagramthen back to Window Autherntication Mode it’s working too.

---

Hi Cutiepie and Nathaniel, I really apprecicate both of your solutions they are really a great source of knowledge.
Thank you so much, it really help me out on SQL Server 2005, it does not happened with SQL Server 2008.
I wish you the best of luck, and keep it up.
Take Care Guys,
Angel Lopez

---

Actually, envirodat helped me with this: I found typin in "sa" as owner also worked and I didn’t have to change thelevel to SQL 2000. Changing to sa was the only thing I had to make. Thanks!

---

]]>