MS SQL – “Create failed for User” Error During Database Restore

When migrating a database, from one location to another, you may encounter a problem with the users. The database backup/restore method does not transfer the users. When you try to create the user after the restore process, you will get:

Create failed for User dbusername. (Microsoft.SqlServer.Smo)

Additional information:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

User, group, or role ‘dbusername‘ already exists in the current database. (Microsoft SQL server, Error: 15023)

This problem is caused by an internal id conflict of the users. It can be corrected by a few lines. For this example, let’s assume your restored database is named EmployeesDB and your user is employeeDBAdmin.

Click on New Query, and type the following.

Use EmployeeDB
exec
sp_change_users_login 'update_one', 'employeeDBAdmin', 'employeeDBAdmin'

Click on ! Execute.

This will synch your users, and you will be able to log into the database as usual.

The full details of this solution can be seen at the Microsoft KB 274188.