Microsoft SQL Server forum - Droping and adding new user

Author

Droping and adding new user

microsoft.public.dotnet.languages.vb

2005-08-23, 1:23 pm

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_logi
n 'Update_One', 'user1', 'user1'

Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_logi
n,
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

2005-08-23, 8:24 pm

microsoft.public.dotnet.languages.vb (mamun_ah@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, esquel@sommarskog.se

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

Sponsored Links