Answered by:
Database diagram support objects cannot be installed...

-
I can't make a diagram on our server(Win2003 x64,SQL 2005 Enterprise x64 sp2 3054).
I have tried to login as an admin and create a new db, but get the same message -
'Database diagram support objects cannot be installed because this database does not have a valid owner. To..'
Also tried to make a diagram from the ReportServer db.
Any suggestions?
Question
Answers
-
Hi,
The real problem is that the default owner(dbo) doesn't have a login mapped to it at all.As I tried to map the sa login to the database owner I received another error stating "User,group, or role 'dbo' already exists...".However if you try this code it will actually works :
EXEC sp_dbcmptlevel 'yourDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N'dbo' REVERT
go
All Replies
-
-
Thanks Meher for your reply.
1. Both of the databases I tried has a owner.
2. I'm pretty sure the compatibility level is 90 (SQL 2005) since one is created during installation(ReportServer) and the other is created as a new db in SQL Management Studio. (To be sure I will check next week)
Any other suggestions?
-
-
-
-
-
Hi olof,
can you please go through the steps outlined in section 4.8.1 in http://support.microsoft.com/?id=910228
As per the section in the article, looks like you might have to install the diagram support.
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.
MVPs, please correct me if I am missing something.
Thanks
Meher
-
one other thing I forgot to mention is that the installation of support objects can fail if the database
has been attached or restored from another instance of SQL Server. In such a case there is a
possibility that the database owner name stored in the database may not be a valid logon for the
instance of the SQL Server on to which the database is restored to (or attached to).
I would suggest if thats the case then you can use the Alter statement to change the owner to a valid logon.
ALTER AUTHORIZATION ON DATABASE::your dbname TO yourdesiredlogin
(please see BOL for more details on ALTER AUTHORIZATION).
I would suggest doing through T-SQL than UI and see if it solves your issue.
Thanks
Meher
-
-
Just wanted to add that I was able to connect and install the diagram support objects by using Visual Studio rather than SQL Server Management Studio -- without having to do the ALTER AUTHORIZATION statement. Steps below:
-
Opened Visual Studio 2005
-
Added the Server and database to the Data Connections on the Server Explorer tab.
-
Click Database Diagrams to expand
-
Received message box: This server does not have one or more of the databse objects required to use database diagramming. Do you wish to create them? Clicked YES
-
Diagrams showed up - Yay!
-
-
-
Hi,
The real problem is that the default owner(dbo) doesn't have a login mapped to it at all.As I tried to map the sa login to the database owner I received another error stating "User,group, or role 'dbo' already exists...".However if you try this code it will actually works :
EXEC sp_dbcmptlevel 'yourDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N'dbo' REVERT
go -
-
I ran the query and the following was returned:
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
sandeep -
I tried this technique using Visual Studio 2008 Team System Developer Edition SP1 and SQL Server 2008 Developer Edition SP1 and got an error:
The message box that pops up after attempting to expand the Database Diagrams node of the tree says:
This database does not have a valid dob user or you do not have permissions to impersonate the dbo user, so database
diagramming is not available. Do you want to make yourself the dbo of this database in order to use database diagramming?
(Yes)/(No)/(Help)
After clicking Yes to the first message, you get a second message:
This server does not have one of more of the database objects required to use database diagramming. Do you wish to create them?
(Yes)/(No)/(Help)
When I click on the Yes button, the following error message pops up:
Could not obtain information about Windows NT group/user '<mydomain>/<myuserid>', error code 0x5.
What a pity this is still a problem in SQL Server 2008 after all these many months for Microsoft to fix it. :-(
Fred Morrison -
-
Hi,
The real problem is that the default owner(dbo) doesn't have a login mapped to it at all.As I tried to map the sa login to the database owner I received another error stating "User,group, or role 'dbo' already exists...".However if you try this code it will actually works :
EXEC sp_dbcmptlevel 'yourDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N'dbo' REVERT
go
This work wery well!!
thx
Maxidag -
-
-
-
-
-
-
-
-
-
-
exelente 100% esta es la mejor respuesta
-
Opened Visual Studio 2005
-
Added the Server and database to the Data Connections on the Server Explorer tab.
-
Click Database Diagrams to expand
-
Received message box: This server does not have one or more of the databse objects required to use database diagramming. Do you wish to create them? Clicked YES
-
Diagrams showed up - Yay!
-
-
-
It worked in sql server 2008, after setting "sa" as Owner.
Right click database in management studio then click properties.
select "files" page
then set owner as "sa"
Of all the solutions here - and I tried them all - this was the only one that worked.Thanks!
Was trying on a local install of 2008 R2, on a database I had created a little while earlier.
-
I had the same problem as you mentioned and I was able to do the following steps to solve the owner issue in MS SQL Management Studio.
Please make sure that you don't select a user name (in step 4 in the figure) that is already Admin for the selected database.Steps:
1. Right-Click on [Database name] -> Properties.
2. Database Properties - [db name] -> Files ->click on [...] button in front of owner.
3. Select Database Owner -> [Browse...]
4. Browse for Objects -> Select one non-admin username5. Hit OK buttons in all windows.
Its all done. -
-
-
-
-