Error while creating a new user

[vaidyanathanpc]

Hi,
I'm trying to create a new user "USER1" from the enterprise manager in SQl Server . Logins --> New Login. I entered the name of the user and gave him the access to my database and hit enter(return) key. I get the error message "Error 15023: User or role 'USER1' already exists in the current database". I checked my database but there are no roles or users with the name 'USER1'. There is no USER1 in the server on which my database resides.

Please help
Thanks in advance
P.C. Vaidyanathan

 

[RickCole]

Just to be on the safe side run the following from a Query analyser window just in case you've got an orphaned user/login name somehow.

select loginname from master..syslogins
select name from master..sysxlogins
select remoteusername from master..sysremotelogins

And

select name from dbname..sysusers

where dbname is the name of your user database

Rick.

 

[Bernadette]

Hi There

It sounds as if you have an "orphaned user" in your database called USER1.
To check this run the following

Use <Insert DB Name>
go
Select suid, name from sysusers
where name = 'USER1'


If it does exist you need to delete it from this table.
To do that run the following .....


User <Insert DB Name>
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
delete from sysusers where name = 'USER1'
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go


When you have deleted the user, Refresh your database through Enterprise Manager.

You can now add the user to your database ..... providing a login exists of course.

Hope this helps

Bernadette

 

[vaidyanathanpc]

Thanks, it worked..

P.C. Vaidyanathan