Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was trying to use Database diagrams is SQL Server and got following error:

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.

I tried to use this command to solve my problem

ALTER AUTHORIZATION ON DATABASE::mydb TO "domain\username"
GO

but it did not help. When I changed my domain account to sa and ran

ALTER AUTHORIZATION ON DATABASE::mydb TO sa
GO

I got rid of this error. The question is, why domain account is not acceptable for database owner (it seems that only sa annd NT AUTHORITY\SYSTEM are acceptable)?

share|improve this question
    
i m getting this error. TITLE: Microsoft SQL Server Management Studio ------------------------------ This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools) ------------------------------ BUTTONS: OK ------------------------------ –  zonayed Jun 25 '13 at 10:28
add comment

2 Answers

up vote 4 down vote accepted

You don't need to be sa or another sysadmin account to create database diagrams, but I think you may need to do this in order to initially install database diagram support.

If you do this as sa, you should then be able to let domain\username create diagrams. Most of the resources online will tell you that if domain\username is the database owner it should work, but there is a slight difference when you just set a login as the owner of a database and they aren't already a user inside the database: they get mapped to the special dbo account. I seemed to be able to do this in SQL Server 2012, but I used SQL accounts not domain accounts. So I don't know if that's what is causing your issue, but I think I have provided a valid workaround.

share|improve this answer
    
Thanks, very good answer! Now I understand how it works. –  jrara Aug 25 '12 at 17:53
add comment

A domain account can be a database owner no problem:

In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database.

Generally when I have come across this issue it has been to do with the fact that the database you want to create the diagram in is set to Compatibility Level 80. This will not work. Check your compatibility level and set it to 90 at a minimum. This should resolve your issue.

I hope this helps.

share|improve this answer
    
The Compatibility Level is set to 100, I'm using SQL Server 2008 R2. –  jrara Aug 25 '12 at 11:01
    
Is the instance in the same domain as you? –  Mr.Brownstone Aug 25 '12 at 11:19
    
Yes it is...... –  jrara Aug 25 '12 at 11:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.