User Already Exists in the Current Database

May 19, 2017 Jordansphere MS SQL

Scenario: I was transferring a database (mick) from one SQL2005 server to an SQL2012 server. During the mapping of a user login (mick) I got the following the error:

sql_db_permissions

Create failed for User 'mick'
User, group, or role 'mick' already exists in the current database
 

Answer:

 
In Studio Management you can use the follwing command :
 
syntax:
sp_change_users_login 'AUTO_FIX', 'USER_NAME'

In my case:

sp_change_users_login 'AUTO_FIX', 'mick'

 You should see the following output:

The row for user 'mick' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

 

 

Note: 

 
The problem arose because I  restored a DB from another server which had the same user (mick). This command fixes the orphaned user.
 
To get list of orphaned user for a particular database you can use the following command:
 
EXEC sp_change_users_login 'Report'

 

 

DB migratiion, MS SQL 200, MS SQL 2012, User Already Exists in the Current Database,

Powered by WordPress. Designed by elogi.