Droping and adding new user

[microsoft.public.dotnet.languages.vb]

Hi All,

I am having a serious problem of removing and adding again an user in a
database.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack

Somehow, the user was created earlier but was not able to run query
assigned to him. As a result I wanted to drop and recreate the user.

I have done many possible things to create the users again with the
same user id but failed.

1. I tried to delete this user from the enterprsie manager security-->
logins-->user1
It deletes but when I try to add again, it gives me error message
(Error 15023: user or role u'user1' already exist).

2. Then I tried in the db:
delete sysusers where name='user1'
it deletes the user1.

3. Again tried adding, got the message in 1.

4. Then I tried

use db1
EXEC sp_change_users_login 'Update_One', 'user1', 'user1'

Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,
Line 88
Terminating this procedure. The User name 'user1' is absent or invalid.

I also tried master database and ran the following.

EXEC sp_droplogin 'user1'
The login 'user1' does not exist.

But If I try to add the login user1, get the error message.
Error 15023: user or role u'user1' already exist

I also ran the following when I got Ad hoc error message
execute sp_configure "allow updates",1
go
reconfigure with override
go
Could you please tell me how I can solve this problem.

I do highly appreciate your help.

Thanks a million in advance.

best regards,
mamun

[Erland Sommarskog]

microsoft.public.dotnet.languages.vb (ma******@hotmail.com) writes:

2. Then I tried in the db:
delete sysusers where name='user1'
it deletes the user1.

You did what? You should only perform operations on system tables if
instructed so by a Microsoft Support Professional, or if you know
exactly what you are doing.

I would recommend that you open a case with Microsoft to sort this out.
Most likely, this will require more manual repairs, and I certainly
does not want to try to give directions from a distance.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp