Error 21776: [SQL-DMO] The name 'dbo' was not found in the users.... 3

[djhawthorn]

I am trying to install SP3a on MS SQL Server 2003, and it failed to authenticate using either Windows Authentication(a domain administrator account), or the SA logon.

(I definately have the correct password when using the SA account - I verfied the password because I can use an ODBC DSN under the SA account to connect to the databases).

I try and look further into why I can't authenticate, and I find this error appears in Enterprise Manager...

Error 21776: [SQL-DMO] The name 'dbo' was not found in the users collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

...whenever I:
- I double click on the SA Login
- I double click on the BUILTIN\Administrators Login
- I attempt to change the SA Login password

I found two MS articles on this problem, namely:

http://support.microsoft.com/kb/q218172/

http://support.microsoft.com/default.aspx?scid=kb;en-us;305711

And I have attempted to change the database owner of all the databases to the 'sa' user, as suggested. I have one problem here - I have two databases that have spaces in the name, and I can't seem to USE those databases using isql - it keeps telling me I am using incorrect command line syntax. I have tried:

USE database name
USE 'database name'
USE "database name"
USE `database name`

and none seem to work. What's the problem here?

A couple of other things I should mention about the setup:
- I do have a 'dbo' user listed.
- I have restored some databases from another server onto this one; which is why the problem probably occurred in the first place.
- When I try to edit the DBO user and give it some database access, it gives me one of two errors, depending on the database:
--- "Error 15023: User or role 'dbo' already exists in the current database"
--- "Error 21002: [SQL-DMO]User 'dbo' already exists"

Anyone know how to solve any of these problems?

MCSE NT4/W2K

 

[gmmastros]

use [db name with spaces]

Needs the square brackets.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[djhawthorn]

Thank you!
I did that, stopped and restarted the MS SQL Service, and that's fixed the first problem - I no longer get that 21776 error when I access the SA or BUILTIN\Administrators Logons.

I am still getting the other two errors when I try and add the dbo user to the database - its still an orphaned user. The errors again are:
- "Error 15023: User or role 'dbo' already exists in the current database"
- "Error 21002: [SQL-DMO]User 'dbo' already exists"

I have read one or two KB articles about the command:

sp_change_users_login 'auto_fix', 'dbo'

Which supposedly fixes orphaned users like this, but SQL returns the error:

Msg 15287, Level 16, State 1, Server SQL, Procedure sp_change_users_login, Line 39
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.

If I can install SP3a (which I wont be able to try for 24 hours) this may not be a huge issue, but really would like to get it resolved if anyone knows how to..?

MCSE NT4/W2K

 

[gmmastros]

dbo usually refers to Data Base Owner. In fact, you can have multiple tables with the same name but with different owners. So, my point is that dbo is a reserved word. If you have a user named 'dbo', you should put square brackets around that also.

sp_change_users_login 'auto_fix', [dbo]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[djhawthorn]

Same error, regardless of if I use
sp_change_users_login 'auto_fix', 'dbo'
or
sp_change_users_login 'auto_fix', [dbo]

I should point out that I didn't create this dbo user. It is just there (just like the sa user is there). I am guessing that its a standard/built-in user when you install SQL Server?

Because I moved the databases across from one server to another, the SID for the dbo user that has access to the databases does not match the SID for the dbo user on the new server, (its still the SID from the dbo user on the old server), thus the orphaned problem.



MCSE NT4/W2K

 

[djhawthorn]

I tried running SP3a setup again, and it continues to fail to authenticate.

Anyone know of this problem? What might cause it?

MCSE NT4/W2K

 

[MissTipps]

Which login does dbo map to in this database? Your sp_change_users_login sp is failing because it cannot be used with the dbo user.

I would run sp_changedbowner so that you can explicitly choose which login will own this particular database. You will then know for definite which login dbo maps to and any orphaning problems should then be largely irrelevant.

 

[djhawthorn]

I've run the sp_changedbowner - sa now owns ALL databases on the server.

I guess its just a question of, does the built-in DBO user require access to the databases? If it doesn't, then what I say next is largely irrelevant.

If I try and add the built-in dbo user to the database, I get one of the two aforementioned errors:
- "Error 15023: User or role 'dbo' already exists in the current database"
- "Error 21002: [SQL-DMO]User 'dbo' already exists"

But the 'dbo' user it refers to is an orphaned user - its the SID from the old server's dbo user, not the current servers dbo user. I can't find any way to fix this problem.

MCSE NT4/W2K

 

[djhawthorn]

I just noticed something; my sa user is a dbo user.
I also have a seperate dbo user...

If I look at Databases -> master -> Users, it shows:
Name: dbo
Login: sa

If I look at Security -> Logons, it shows:
- sa
- dbo
- [whole host of other users]

The dbo in Security -> Logons is the one I am trying to add to the databases when I get the errors; presumeably I can remove this user - its the obsolete one?


Also, I STILL cant install SP3a. I can't authenticate using either the sa account, or the domain administrator account. The temporary paths are C:\temp\temp (user) and C:\windows\temp (system), and I'm running the SP3a setup files from the default extraction path C:\sqlsp3\ folder - so there are no spaces in the temp or installation paths. (I noted some web sites saying spaces in the path would cause problems).

Any one know why I can't authenticate to install the service pack?

MCSE NT4/W2K

 

[MissTipps]

The 'dbo' login you have should be deleted.

It is important that you get to grips with the difference between a user and a login. A login is server wide and provides authentication to the SQL Server. Users are created on a 'per database' basis and each database user should map to a login which you have created. 'dbo' is a database user and it is created and given that name at the time the database it owns is created.

There is no need (and it only adds to the confusion) if you have a SQL login also named 'dbo'.

With regard to the SP3a problem - what is the exact error message you get?

 

[djhawthorn]

The error message is a generic "Could not authenticate to the SQL server using the sa password you supplied. Please check it and try again" or something (I can't run the SP installation to get the exact error as it shuts down the server).

In the service pack log, it also shows this:

Setup was unable to verify the state of the server for an upgrade. Verify the server is able to start and that you provided a valid sa password and restart setup.

The sa password I use is correct. I also try the Domain Administrator account (that I am logged in as) and that fails as well. The service will quite happily start and stop at my service control request.

MCSE NT4/W2K

 

[MissTipps]

This does look like a tricky one. I found some information here:

http://www.aspfaq.com/show.asp?id=2440

Don't know if you've already seen it, but checking out the 'Other errors during setup' refers to the error message you are receiving. It basically points you to manually install MDAC, then manually install SP3, which could be the way to go. Let us know if it works out.

 

[djhawthorn]

Thanks for the link; didnt help though.

I tried everything on it; MDAC was already the latest version (its running on Windows Server 2003), the SQLSERVR.EXE was newer than the version it specified, DBNETLIB was already set in the registry, the KB article I had seen (and tried).

The single user mode option I hadnt thought of, but tried it and it didnt help either

Gah!

I'm considering forgetting about it / waiting for SP4 (if it comes out). The server is running at the moment; better that than have it break from trying to get a service pack installed.

MCSE NT4/W2K

 

[Hablaras]

SP4 is out

Hopefully that solves your problems...

 

[djhawthorn]

Really? Since when?

Shows how much attention I pay.

MCSE NT4/W2K

 

[djhawthorn]

Since 5/6/2005 - about 5 days ago.
Gold, thank you! Will try and if I have problems, will post back .

MCSE NT4/W2K

 

[srussellpatriottran]

I had the same error. I created 2 new databases and neither had user dbo.
My solution:
1. Script out all users for both DB.
2. Delete all users from both DB.
3. Run exec sp_changedbowner 'sa' for both DB.
4. Refresh to verify both DB to verify dbo user exists. 5. Run script to create previously deleted users and issue grants.