Hi All,

I have deleted an user from a SQL server database, after deleting I
refreshed the list the user name disappeared,

But when I try to add a new user to the database in login name box -
the deleted user still shows and if I select this user, it gives me an
error that the user already exists in the database. And the user still
cannot connect to the database.

Help..

Thanks,

Hi Sunita,

What you have probably done is dropped the user from a specific database
without dropping the login from the SQL Server instance. If you delete the
user from the database in Enterprise Manager, it is effectively that same as
calling the sp_dropuser stored procedure in the same database for the user
you are deleting. This does not remove the login from the instance.

If you want to remove the login from the SQL Server instance, you should
call the sp_droplogin stored procedure with that user's name. Then you will
be able to ad that login name again.

If you have a user with a login of 'USER1' in database 'Pubs' and you want
to remove that login from the server, you can connect to the server using
Query Analyzer and execute the following commands:
1. use pubs
2. sp_dropuser 'USER1'
3. sp_droplogin 'USER1'

For specifics on how to use the sp_droplogin stored procedure, you can look
up sp_droplogin in the SQL Server Books Online, or look it up on MSDN at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_drop_2q0e.asp.

I hope this helps,

Brooks
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

[quoted text, click to view]