Database Diagram Support Objects cannot be Installed ... no valid owner

Question

I tried to create a database diagramm with SQL Server 2008, but an error occurs:

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.

Then I tried the following:

EXEC sp_dbcmptlevel 'Ariha', '90';
GO
ALTER AUTHORIZATION ON DATABASE::Ariha TO [WIN-NDKPHUPPNFL\Administrator]
GO
USE Ariha
GO
EXECUTE AS USER = N'dbo' REVERT
GO

Next erorr pops up:

Msg 15404, Level 16, State 11, Line 1 Could not obtain information about Windows NT group/user 'WIN-NDKPHUPPNFL\Administrator', error code 0x534.

The Problem is the name of the PC has changed into "DevPC" I also changed this in the update script, but still the same error 15404.

What can I do to fix this annoying error?

Helminthic

You also get this problem when you restore a backup from a different machine. – Mendoza

When you restore a db from another machine, you can fix this error by running two commands: 1) alter authorization on database::[db_name] to [sa], and then 2) run the same command again and replace [sa] with the user who owned the database immediately after the restore. – Kilowatthour

Answer 1

You should consider SQL authentication account for database ownership; then you don't have to worry about accounts coming and going, databases or instances moving to different servers, and your next PC name change. I have several systems where we use:

ALTER AUTHORIZATION ON DATABASE::Ariha TO [sa];

Or if you want to change the owner to that local Administrator account, then it should be:

ALTER AUTHORIZATION ON DATABASE::Ariha TO [DevPC\Administrator];

Because renaming the machine to DevPC has eliminated the local account that used to be named WIN-ND...\Administrator and this has also invalidated the current owner of the database.

If SELECT @@SERVERNAME; is not accurate (it should say DevPC), then in order to ensure that your server rename has taken hold within SQL Server, you may also want to issue the following:

EXEC sys.sp_dropserver @server = N'old server name';
GO
EXEC sys.sp_addserver @server = N'DevPC', @local = N'local';
GO

Laryngeal

it shows: "WIN-NDKPHUPPNFL" I used your sql statement and it works ... but is this setup correct or do I have to do anything else more? – Helminthic

Well, that really depends. If you need your database to be owned by a Windows domain/workgroup user, then there is probably more work to do. Personally, I find this problematic. Maybe read this first: sqlblog.org/blogs/tibor_karaszi/archive/2009/12/30/… – Laryngeal

Are there any issues with setting it to sa if you have sql authentication disabled? – Mendoza

@AaronBertrand Hi I have a question.By default If we create a database database owner is set to current windows user and if current windows user is also administrator. Why we need to change it to Sql Authantication Login . Indeed I can not understand this error. If we change it to sa login it works. But Administrator Windows Login is also System Admin. Could You Plase help? – Mond

I restored a bunch of databases, and all of them displayed the error in the OP's question, even though the properties of each database showed a valid user. I used the 1st command to change it to [sa] and then I changed it back to the user that it said it was before, and viola, no more errors. Not sure why a database restore did not properly set the database owner... – Kilowatthour

Answer 2

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 Drop down at right labeled "Compatibility Level" choose "SQL Server 2005(90)" 3-1. choose "SQL Server 2008" if you receive a comparability error.
4. Go to the Files Page
5. Enter "sa" in the owner textbox. 5-1 or click on the ellipses(...) and choose a rightful owner.
6. Hit OK

after doing this, You will now be able to access the Database Diagrams.

Hydrothorax

Is there any reason why a database should NOT have an owner? I'm coming into someone else's product database and there is no owner set. Was this intentional? (There's no one at the client who would know). – Shoshone

The database should have an owner. Choosing "sa" instead of a rightful owner is simply an easy choice to fix the error above. But choosing a rightful owner also fixed the problem. – Kitten

Could someone explain the purpose of changing the compatibility level in these steps? Just setting the owner under files to 'sa' was sufficient for me (even though I don't have sql auth enabled, oddly). – Mendoza

This worked for me. I did need to remove my user from Databasename > Security, though. – Christianity

This worked for me, on SQL Server 2017, using Management Studio 17.9, without need of step 1 – Phase

I only had to set the owner to get it working, had no need to tinker with the compatibility levels – Decurrent

@JasonKleban Just because the database was added to MSMS by manual – Patter

@LeslyRevenge This worked for me. I'm using SQL Server 2019. Thanks – Bast

Answer 3

USE [ECMIS]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

It works.

Auction

Answer 4

Select your database - Right Click - Select Properties

Select FILE in left side of page

In the OWNER box, select button which has three dots (…) in it

Now select user ‘sa and Click OK

Conservatoire

Answer 5

Enter "SA" instead of "sa" in the owner textbox. This worked for me.

Intelsat

Answer 6

I had the same problem.
I wanted to view my diagram, which I created the same day at work, at home. But I couldn't because of this message.
I found out that the owner of the database was the user of my computer -as expected. but since the computer is in the company's domain, and I am not connected to the company's network, the database couldn't resolve the owner.

So what I did is change the owner to a local user and it worked!!
Hope this helps someone.

You change the user by right-click on the database, properties, files, owner

Tolerance

Answer 7

This fixed it for me. It sets the owner found under the 'files' section of the database properties window, and is as scripted by management studio.

USE [your_db_name]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

According to the sp_changedbowner documentation this is deprecated now.

Based on Israel's answer. Aaron's answer is the non-deprecated variation of this.

Mendoza

Answer 8

I just experienced this. I had read the suggestions on this page, as well as the SQL Authority suggestions (which is the same thing) and none of the above worked.

In the end, I removed the account and recreated (with the same username/password). Just like that, all the issues went away.

Sadly, this means I don't know what went wrong so I can't share any thing else.

Illfavored

Answer 9

1.Right click on your Database , 2.Then select properties . 3.Select the option in compatibility levels choose sql 2008[100] if you are working with Microsoft sql 2008.

4.Then select the file and write ( sa ) in owner`s textbox

100% works for me.

Matthias

Answer 10

An easier way to solve this issues would be to right click the name of your database, choose "New Query", type " exec sp_changedbowner 'sa' " and execute the query. Then you'll be good to go.

Belize

Answer 11

you must enter as administrator right click to microsofft sql server management studio and run as admin

Defendant

Answer 12

Only need to execute it in query editor ALTER AUTHORIZATION ON DATABASE::YourDatabase TO [domain\account];

Amieva

Answer 13

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

Holms

Answer 14

right click on your Database , then select properties . select the option in compatibility levels choose sql 2005[90] instead of 2008 if you are working with Microsoft sql 2008. then select the file and write ( sa ) in owner`s textbox. it will work probably

Ouabain