Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 re-added user but cannot assign User Mapping

Author  Topic 

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2014-07-22 : 22:43:21
Hi,

I have one userid was dropped from Login. But there was request to re-add his login back. Problem is after i created the login (same login before drop), when i was trying to check (add) the UserMapping (where we pick the DB) i got below error :

User, group, or role 'choo' already exists in the current database. (Microsoft SQL Server, Error: 15023)

If this user already mapped with that DB, why the checkbox is not TICK?
please help me

~~~who controls the past controls the future, who controls the present controls the past. ¯\(º_o)/¯ ~~~

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-23 : 08:43:10
The issue is that a user already exists in the database, but the SIDassociated with the user does not match with the SID of the login that you created.

You can go about fixing it two ways. One is to delete the database user (in object explorer open up the node for the database and then security node, users, find the user, right click and delete). Then, you will be able to add the user just as you were trying earlier.

Another way is to associate the user with the login. To do that,
USE YourDatabaseName
GO
sp_change_users_login @Action='update_one', @UserNamePattern='choo', @LoginName='choo';
You can find any users in the database that are orphaned - i.e., don't have a login associated with them using this:
sp_change_users_login @Action='Report';
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-07-23 : 14:19:33
A third option is to delete the login - and recreate the login with the same SID that exists for the user in the database. You can get the SID from the DMV sys.database_principals and specify that SID using CREATE LOGIN.
Go to Top of Page
   

- Advertisement -