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
The owner was set to a valid login, being the windows auth. login i was logged in with when i create the database. I tried using ALTER AUTHORIZATION to change it to another SQL login that i'd created for future use with this and other related databases, but it still gives me the same error.
Any ideas what's going on?
brian
I haven't been able to reproduce a case where setting the database owner didn't solve the problem.
I'm suspecting this has something to do with the upgrade process. When databases are upgraded from SQL Server 2000 to SQL Server 2005, they are automatically set to SQL Server 2000 compatibility mode.
As an experiment, can you try setting the database to SQL Server 2005 compatibility mode before creating the first diagram? (This can be done in the Files page of the Database Properties dialog - right click on the database in Object Explorer and select Properties...) You can set the database back to SQL Server 2000 compatibility after this experiment.
Please let me know if this makes any difference.
|||Hi,I have the exact same problem when trying to create diagrams. Everytime I click on "Diagrams" I get the error message mentioned above. I tried changing the compatibility level, no effect.
I'm logged in with my windows user, I am sysadmin on the server and the db owner is set to my windows user...
I even created a brand new database (I presume the compatibility level is set to SQL Server 2005, but I haven't checked) and the diagrams still don't work.
Any hints would be highly appreciated.
Thanks,
Florin
|||I've filed a defect report for this issue.
Just to make sure I'm understanding you correctly:
1) You are working with a SQL Server 2005 (or SQL Server 2005 Express) server
2) Your database is in SQL Server 2005 compatibility mode
3) Your database is read/write
4) Your database is owned by a valid login
5) You are logged in as an administrator
6) You are allowing Management Studio to try to install the diagram support objects
7) You don't get any error messages
8) You still can't create diagrams
I've been able to install the diagram support objects when 1 - 6 above were true, so this is definitely not expected.
As an experiment, could you create a new SQL Authentication login, create a new database on your SQL Server 2005 server, make the new login the database owner, and then try to create a diagram in the new database?|||I was having the same problem. I had upgraded a db from SQL Server2000. The error I kept getting was that I was a type user and I could not be a db_owner. Here is what I did to resolve the issue (hacked it out):
1. Removed all schema's and logins associated with my login in each database
2. Removed all user mapping associated with my login under the server's Security;Logins folder (I think this is where the main problem comes in) under User Mappings
3. Gave myself Sysadmin Role in samefolder under Roles
4. went back to the database I was trying to create diagrams on and in the properties dialog under Files changed the owner to my domain login.
And it worked! When I got the success and exited the properties dialog I clicked on the Database Diagram folder and was met with the component instalation dialog and continued on.
I did notice back in the Security folder for the server that User mapping in the Security;Login folder that it mapped my database to a User of dbo and default Schema of dbo.
Hope this helps. - Jeff|||Hi,
Yes, I allowed the SQL Server 2005 Management Studio to install the diagram support, but the message I get is that this database does not have a valid user (which is my windows user: domain\florin). Once I changed the owner to 'sa' without having to login as 'sa', it worked...
I hope that helps in narrowing down the problem.
Thank you,
Florin|||
When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)
The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.
There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)
Thank you very much, Steven,
For me the simple solution was, as you suggested, to change compatibility level from 80 to 90 and then back to 80.
Michelle.
|||Thanks Steve,
I got the same probelm when I restore my database from SQL2000, I couldn't open my diagram. You solved my issue very simply..
thank u very much once again
Sree
|||I found that it was because the DB i had restored had a diagram in it from SQL Server 2000.
This fixed it;
1) Switch to compatibility mode 9.
2) Remove my old diagrams.
3) Switch back to compatibility mode 8.
I also ran this command.
ALTER AUTHORIZATION ON DATABASE::database_name TO [domainname\username]
|||I'm having a similar problem with SQL Server Express in Visual Studio.
The database owner shows as my old domain user logon name. I changed that name in Active Directory a few weeks ago but somehow the database is still picking it up despite being logged in under the new name. I can not access Database Diagramming.
If I log on to the pc with a second domain user account, everything works fine with diagramming because the database owner changes and shows the second user's logon name as the database owner and it matches the actual user logon name.
You say this can be fixed by "explicity setting the owner to a valid prinicipal on the server." How can I do this with SQLexpress?
Thanks,
rich
|||I couldn't get ALTER to work at all on my SSMSE on SQLexpress but that's most likely because I don't know what I'm doing with ALTER. I did however cure my problem, and hopefully didn't cause some other problems by deleting the SQLExpress folder and restarting SQL Server service. See my complete answer here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329888&SiteID=1
rich
|||Hello,
I had the same problem without upgrading a database.
I did change the Compatibility level to SQL Server 2005 before creating the first diagrams and it worked.
Even if you change then the Compatibility level back to SQL Server 2000 its ok.
Thanks.
Alex.
|||Hi Michelle,
Are you Michelle Gutzait, a CPA from Illinois? If so, please e-mail me back at the address below.
Thanks,
Ron
E-Mail: tdi358@.yahoo.com
|||I found this post helped me:
http://dotnet.org.za/pieter/archive/2006/03/23/51066.aspx?CommentPosted=true
When doing this, make sure you are connected to the network if you are using a windows account.
No comments:
Post a Comment