Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

SQL User Mappings error

After copying a database and security information to a new server, I'm trying to map a user ('UserX') to a database in SQL.  However, when attempting to save, I get an error message: User, group, or role 'UserX' already exists in the current database.  UserX does exist in the database.  I tried to delete the user in the database, but the user is tied to a database schema.  Attempting to delete the schema...already a likely bad idea...fails tied to an object named 'admins'.  How can I get 'UserX' mapped to the database?  What needs to be undone and reconstructed?

Thanks in advance.
0
DaneBigham
Asked:
DaneBigham
1 Solution
 
ste5anSenior DeveloperCommented:
Take a look at the system procedure sp_change_users_login.

List unmapped users:
EXECUTE sp_change_users_login 'report';

Open in new window


Repair a user:
EXECUTE sp_change_users_login 'update_one', 'username', 'loginname';

Open in new window


Where username is the name of your UserX and loginname is the name of an existing login, which the user should be mapped to.

btw, when this error occurs in SSMS dialog, this can happen. In such a case use T-SQL (CREATE LOGIN and CREATE USER FOR LOGIN).
0
 
arnoldCommented:
The issue as was pointed out is two fold.
1) the database backup includes the security settings for the database which includes the SID of the users, groups; however
2) the sql server security is where the logins are actually stored.

when migrating databases from one server to another, creating a user requires the use and setting  of the SID to match the one in the database. MS provides an SP that deals with listing the users/password/sids on the original server with a create user directive that can be copied and used to create the user on the new system.

Here is the link to the MS SP..
https://support.microsoft.com/en-us/kb/918992
0
 
DaneBighamAuthor Commented:
This worked perfectly.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now