recreate user

[stephenUK]

Hi,

I'm having a problem with a user account on SQL Server 7 that i am trying to recreate.

Basically, I have deleted a user (sp_dropuser) which seems to have worked ok. But now, when I try to add the user again (based on a new NT account) it complains that the user already exists.

It no longer appears when I try sp_helpusers, and doesn't exist in the sysusers table - so where is it?

Thanks in advance

Stephen

 

[dbmsguy]

you could try doing:

use dbname
go
select * from sysusers

this, instead of the sp_helpuser might show a different result. could you possibly have an inconsistency between syslogins and sysusers? Paul

http://www.dbmsguy.com

 

[wjbburn]

Try this.

EXEC sp_change_users_login 'Auto_Fix','login'

The login should equal the user and you should run this against each of the databases where the user previously existed. You will probably need to close and reopen enterprise manager is you our using that to add the user.

 

[stephenUK]

Many thanks to dbmsguy and wjbburn for responding.

The sp_change_users_login solution seems to have worked.

Thanks a lot!