Solved

sql server 2005 alter autorization diagram

Posted on 2008-10-18
3
702 Views
Last Modified: 2008-11-22
sql server 2005 alter authorization
0
Comment
Question by:082156r
3 Comments
 
LVL 18

Accepted Solution

by:
sk_raja_raja earned 50 total points
ID: 22749559
To install database diagram support in SQL Server Management Studio, databases must be in SQL Server 2005 database compatibility level. Database compatibility level can be reset after diagram support is installed. If the databases are not in 2005 compatibility level, the following error occurs when you attempt to install diagram support:

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.

To create database diagrams, change the database compatibility level to 2005, install database diagram support, and then return the database to the desired database compatibility level. For more information, search for "sp_dbcmptlevel" in SQL Server Books Online.

Installation of Database Diagram Support Objects Requires a Valid Logon Account

In SQL Server 2005, database diagram support objects will be installed on a database if a member of the db_owner fixed database role performs one of the following operations:
•      Expands the Database Diagrams folder      
•      Creates a new diagram      
•      Explicitly chooses to install the objects from the context menu      
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 that the database is being attached or restored to. 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.

ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now