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
