I frequently get this message when I restore databases from other instance and try to add a new user.
SQL SERVER Error 15023: User already exists in current database.
When you restore a database, the existing users aren’t mapped to the corresponding logins at the instance where it is restored to and are termed as orphaned users. When you try to login to the restored database with an orphan user you get message saying login failed for <user> . The fix is straight forward.
First of all run following T-SQL in Query Analyzer get a list of existing users in the database.
Use YourDB GO EXEC sp_change_users_login 'Report' GO
Note that SQL Server 2012 is the last version to support sp_change_users_login. So you can use the SQL block below to get a list of orphaned users.
SELECT dp.name As Orphan_Users
FROM sys.database_principals dp
left join sys.server_principals sp
ON dp.sid=sp.sid
WHERE sp.name IS NULL AND dp.type='S'
AND dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys')
Now you have the name of the orphanage user, we can find a home for it.
EXECUTE sp_change_users_login 'Update_one', 'userlogin1', 'userlogin1' --Again for SQL Server versions beyond 2012, you can use the SQL below. ALTER USER WITH LOGIN = 'userlogin1'
These queries use the sp_change_users_login and ALTER USER to map the user to login. This will associate the login with the username.
That should be it, you can now use the once problematic login.
