Cannot Create Database Diagram with SQL Server 2005
Posted by kiranpatils on January 26, 2008
Cannot Create Database Diagram with SQL Server 2005
Today one of my associate has faced one problem: He wants to create Database Diagram in SQL SERVER Management Studio. But he is getting the error like shown as below.
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.
——————————
BUTTONS:
OK
He asked me to find the solution and I found it. So I am putting it here. So you don’t have to struggle for it.
SOLUTION:
- Just copy below SP and copy it to Management studio
EXEC sp_dbcmptlevel ‘<Dbse name>’, ‘90′;
go
ALTER AUTHORIZATION ON DATABASE:: <Dbse name>TO “<USER Name>“
go
use [<Dbse name>]
go
EXECUTE AS USER = N‘dbo’ REVERT
Go
- Now just change <Dbse name> to your Database name for which you are facing problem and <USER Name> with your username of SQL.
- Now Try it!!! And Enjoy
Happy Database Diagramming
-Kiran
Sue Massey said
I found your site on google blog search and read a few of your other posts. Keep up the good work. Just added your RSS feed to my feed reader. Look forward to reading more from you.
- Sue.
SillyFools said
Thanks a lot.
this solution help me very very much..
Agha Usman said
Yep … did my job great work buddy
Dhwani said
Below is the way for how to do it for windows authenticated user:
EXEC sp_dbcmptlevel ”, ‘90′;
go
ALTER AUTHORIZATION ON DATABASE::[] TO [MANOHAR\Administrator]
go
use []
go
EXECUTE AS USER = N’dbo’ REVERT
Go