User Already Exists in the Current Database?

Have you ever had a time when you needed to move a SQL database to a new server… made your backup… restored to new server… Go to setup the SQL user with permissions and get this…

Create failed for User ‘myuser’. (Microsoft.SqlServer.Smo)
User, group, or role ‘myuser’ already exists in the current database. (Microsoft SQL Server, Error 15023)

Huh… User already exist in database???

This is because the user in the database is orphaned. This means that there is no SQL login id or password associated with the database user.

This is really easy to correct in SQL 2000 SP3 or greater.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

(HT: FileFormat.info)