MS Win2000 SP3
MS SQL Server 2000

I have a database called SALES on a test server. I have backed up this
database.

I have moved the physical file created by the backed to a live server.

I create a new empty database called SALES on the live server.

I restore the backedup database into the SALES database on the live server.

Success.

All tables in the SALES database are owned by the user SALESOWNER.

I attempt to create a login SALESOWNER for this user but the it always
returns Error 21002 User already exists when I click the permit checkbox for
database access.

I have also tried creating the login first then, the create the SALES db and
give permit access to the login. This works, however once I restore the
database permit access is revoke and an attempt to reapply it fails with the
same message.

Any help would be appreciated on this.

Thx

Report this post to a moderator

You have an "orphaned user". Users are stored at the database level so when
you moved the database, you moved the users defined for it. However, Logins
are stored in the Master DB. This means that the users in you database are
not mapped to valid logins in Master.

execute sp_change_users_login 'update_one','<username>','<login>'

Obviously, the login must already exist.
This remaps the user to a login and solves the problem...

"S49" <s49@s49.com> wrote in message news:ezGp1aPgCHA.1760@tkmsftngp12...
> MS Win2000 SP3
> MS SQL Server 2000
>
> I have a database called SALES on a test server. I have backed up this
> database.
>
> I have moved the physical file created by the backed to a live server.
>
> I create a new empty database called SALES on the live server.
>
> I restore the backedup database into the SALES database on the live
server.
>
> Success.
>
> All tables in the SALES database are owned by the user SALESOWNER.
>
> I attempt to create a login SALESOWNER for this user but the it always
> returns Error 21002 User already exists when I click the permit checkbox
for
> database access.
>
> I have also tried creating the login first then, the create the SALES db
and
> give permit access to the login. This works, however once I restore the
> database permit access is revoke and an attempt to reapply it fails with
the
> same message.
>
> Any help would be appreciated on this.
>
> Thx
>
>

Report this post to a moderator