how do I add a user?

Seems like such a simple thing. But I am pulling my hair out.

Using SQL Server 2000, SP4, on Windows 2000 Server.

I'm moving an ASP-built web app from one machine to another. It works on the
old one, not on the new. Specifically, the error I get is:

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'XXX'.

(where I replaced the login name with X's.)


So I am looking on the old machine (the one that works) and the new one, to
see what differences I notice, and sure enough, in the old one, under Users,
I see the user called XXX. So I go to my new one, which doesn't have a user
by that name, and I attempt to add it. But it doesn't let me add it. In
fact, I am not sure how to add it. I right-click on Users, and try to fill
in the user name but it says that that user doesn't exist, so I can't add
it. Which is ridiculous. I mean, how does any user exist if you don't get a
chance to add them?

Middletree  wrote:
> Seems like such a simple thing. But I am pulling my hair out.
>
> Using SQL Server 2000, SP4, on Windows 2000 Server.
>
> I'm moving an ASP-built web app from one machine to another. It works on t
he
> old one, not on the new. Specifically, the error I get is:
>
>  Microsoft OLE DB Provider for SQL Server (0x80040E4D)
> Login failed for user 'XXX'.
>
> (where I replaced the login name with X's.)
>
>
> So I am looking on the old machine (the one that works) and the new one, t
o
> see what differences I notice, and sure enough, in the old one, under User
s,
> I see the user called XXX. So I go to my new one, which doesn't have a use
r
> by that name, and I attempt to add it. But it doesn't let me add it. In
> fact, I am not sure how to add it. I right-click on Users, and try to fill
> in the user name but it says that that user doesn't exist, so I can't add
> it. Which is ridiculous. I mean, how does any user exist if you don't get 
a
> chance to add them?
>
>
>
>

You have to first create a LOGIN, then map that LOGIN to a database as a
USER.  When you moved the database from the old server to the new, how
did you do it?  If you did a backup/restore, you'll have to first drop
the user from the database before mapping the login.  The user SID (an
internal ID) is stamped in the database referencing the user from the
original server.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

I did a detach/reattach.

Your suggestion worked. Thanks!