13

This is pretty confusing. I'm receiving this error whenever I try to add to (or even view) the Database Diagrams node under a Database node within SQL Server Management Studio Express (SQL Server 2008):

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.

Ok, fine. The problem is, according the Management Studio, my login is the owner of the DB. I'm using Windows Authentication when I log into Management Studio, and if I go to the Files page of the Database Properties dialog box, my login is the owner.

So next I figure, ok, maybe I'm not a member of the correct server role to actually be the owner, however, my login is a member of the sysadmin server role, which, according to this MDSN page is a perquisite for owning a database.

So finally I do some googling, and I see people telling me to try executing a statement like this: 

EXEC sp_dbcmptlevel 'yourDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N'dbo' REVERT
go

My problem here is that I don't really understand what this code is doing, and no amount of trying different usernames or database names with the above statement (which appears to execute successfully) seems to fix my problem.

Can anyone think of anything I might be missing?

Niklas
  • 13,005
  • 23
  • 79
  • 119
Rob
  • 25,984
  • 32
  • 109
  • 155
  • This really belongs on ServerFault.com. You'll probably get good answers here, but this issue is more in the realm of a DBA than in the realm of a developer. – David Nov 05 '10 at 02:19
  • @David, that makes sense. I've voted to move it to SF. – Rob Nov 05 '10 at 02:45
  • possible duplicate of [Database Diagram Support Objects cannot be Installed ... no valid owner](http://stackoverflow.com/questions/2043382/database-diagram-support-objects-cannot-be-installed-no-valid-owner) – Tim Abell Oct 16 '14 at 10:19

4 Answers4

28

Have you tried this set of steps, it might solve your problem:

In SQL Server Management Studio do the following:

  1. Right Click on your database, choose properties
  2. Go to the Options Page
  3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
  4. Go to the Files Page
  5. Enter "sa" in the owner textbox.
  6. Hit OK

Assuming this works and you can add DB diagrams, you can then change the owner back to your Windows user name.

Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
  • Well, "sa" works. I can add DB diagrams via that username. But as soon as I change back to my Windows username, I have the same old problem. Why does "sa" work? Furthermore, why are database diagrams so darn hard get permissions to work with? :-) – Rob Nov 05 '10 at 02:43
  • once it works with sa, set your windows account back as the owner and then try to add diagrams under that account. I have a feeling that when the SQL Server was installed you set up sa as the DBA. Now, if you add the Windows account as a DBA while logged in with sa credentials, you should be able to go back to the Windows account and have the privs you need. – Michael Goldshteyn Nov 05 '10 at 13:22
  • 1
    Hmm, how do I set up my Windows account as a DBA? That's the part I think I'm missing.. – Rob Nov 05 '10 at 13:41
  • 1
    Follow the instructions at: http://www.databasedesign-resource.com/adding-users-in-sql-server.html and choose the correct Server Roles and Database Access settings for the account. – Michael Goldshteyn Nov 07 '10 at 13:31
3

Simply try this.

Alter AUTHORIZATION ON DATABASE::YourDbName TO [sa];
Girish Gupta
  • 1,241
  • 13
  • 27
1

This worked for me

ALTER AUTHORIZATION ON DATABASE ::DATABASE TO sa
Paul Roub
  • 36,322
  • 27
  • 84
  • 93
johxnx
  • 11
  • 2
0

This will work;

ALTER AUTHORIZATION ON DATABASE::yourDB TO NT AUTHORITY\SYSTEM

But this is only for temp.. solution, for any reason if you have to restore DB then the problem will be present..

Dardi
  • 1