Saturday, February 25, 2012

Accidentally dropped DBO from database

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 fro
m
production to dev.
Can I readd this user or is the only solution is to restore from backup?
Thanks,
LindaYou 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 fr
om
>production to dev.
>Can I readd this user or is the only solution is to restore from backup?
>Thanks,
>Linda|||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:
>
>|||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:
[vbcol=seagreen]
>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:
>

No comments:

Post a Comment