Re: Accidentally dropped DBO from database

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 01/20/05 

Date: Thu, 20 Jan 2005 10:43:22 -0700

Actually, what I suggested is exactly how you fix the issue.
Without any information on what happened or what errors you
had, it's hard to give you more specifics.
 If you were receiving the error:
Error 21776: [SQL-DMO] The name 'dbo' was not found in the
Users collection. If the name is a qualified name, use [] to
separate the various parts of the name, and try again
That error is due to having databases where the owner (dbo)
is mapped to a login that doesn't exist on the server. It's
not a matter of DBO being dropped but rather problems with
the mappings.
If you get an error along the lines of "the user is already
a user in the database" when using sp_changedbowner, that's
generally due to a mismatch between dbo and the database
owner. You can work around this by creating a temp login,
change the owner to this new login, then change the owner to
the login you actually want to be the owner and drop the
temporary login.

-Sue

On Thu, 20 Jan 2005 08:57:06 -0800, "lslmustang"
<lslmustang@discussions.microsoft.com> wrote:

>The user dbo was dropped from sysusers. This did not solve the problem. I
>ended up restoring the database from backup but that took several hours and I
>had been hoping a quick script to add dbo back to sysusers might solve it.
>
>"Sue Hoegemeier" wrote:
>
>> You can use sp_changedbowner and dbo will map to whatever
>> login you assign as the database owner with
>> sp_changedbonwer.
>> You can find more information on sp_changedbowner in SQL
>> Server books online.
>>
>> -Sue
>>
>> On Tue, 18 Jan 2005 06:25:03 -0800, "Linda"
>> <Linda@discussions.microsoft.com> wrote:
>>
>> >I altered a script that drops all users from a database and accidentally
>> >changes an AND to an OR so that the user DBO was dropped after a refresh from
>> >production to dev.
>> >
>> >Can I readd this user or is the only solution is to restore from backup?
>> >
>> >Thanks,
>> >Linda
>>
>>

Relevant Pages

  • Re: Confused about dbo
    ... Make sure you understand the difference between login names and user names. ... database within a SQL Server instance. ... There are several ways you could have the username dbo. ... the true owner of the database. ...
    (microsoft.public.sqlserver.security)
  • Re: db_owner role in SQL 2k
    ... The owner of a database is a login, which is listed in the sysdatabases ... I try not to think of DBO as the Database Owner, but just as special, ... For anyone to use any database, their login name must have been given access ...
    (microsoft.public.sqlserver.programming)
  • Re: Login Name is missing
    ... Database ownership determines to login mapping for the 'dbo' user. ... > I see 'dbo' listed as the OWNER of all the tables. ...
    (microsoft.public.sqlserver.security)
  • Re: properties owner and users owner.
    ... 'dbo' is a special database user and must exist in every database. ... normally be the same login but can get out-of-sync in some situations, ... owner entries are synchronized: ...
    (microsoft.public.sqlserver.server)
  • Re: Who should I make the owner?
    ... Every login gets mapped to a username in every database they have access to. ... DBO is a special user name that exists in every database. ... NOT to think of DBO as meaning 'database owner' since many different logins ...
    (microsoft.public.windows.server.general)