Pages

Search This Blog

Tuesday, November 11, 2008

Database diagram support objects cannot be installed because this database does not have a valid owner.

I have restore a database from MSSQL 2000 to MSSQL 2005. When i try to open the database diagram, error message below pop up:

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.

ALTER AUTHORIZATION ON DATABASE: Database_name TO valid_login

I use SSMS to solve my problem.
In SQL Server Management Studio do the following:
  1. Right Click on your database, choose properties
  2. Go to the Options
  3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
  4. Go to Files
  5. Type "sa" in the owner textbox
  6. Click OK
This solve my problem.

Alternatively, we can solve it using TSQL:
- My database name: avlDB
- MY Login: sa

EXEC sp_dbcmptlevel 'avlDB', '90';
GO
ALTER AUTHORIZATION ON DATABASE::avlDB TO "sa"

GO
USE [avlDB]
GO
EXECUTE AS USER = N'dbo' REVERT
GO

8 comments:

Anonymous said...

Thank you! Your suggested procedures workded in SQL 2008 also.


I like to give you 5 stars

Anonymous said...

Thank you, it sloved my problem.

I will give rating 5 to this.

Lam Hoang said...

thanks so much
It's helpful!

Let's have a look with my blog:
http://tcforecast.blogspot.com

ASP.NET Solution said...

The reason for this error is because the database backup was taken from different machine.To fix this issue, we need to add a valid user account as owner for this database... refer to this link for more details

http://www.a2zmenu.com/MySql/SQ-Database-Diagram-Issue.aspx

TechWriter Atoms said...

Thanks very much !

TechWriter Atoms said...

thanks very much !

Pranav Aggarwal said...

It did helped heaps. Thanks a bunch.

Unknown said...

Works in SQL Server 2012. Thanks.