This error usually happens after you have restored a database from a backup from one server to another. When you move a databases from one SQL Server to another, and your application might get errors saying “Login failed for <user>” and when you check Database Security and you find that login was already in the database with appropriate rights to allowed objects in the database but you still end up this Error.
Error 15023: User already exists in current database.
Cause : The problem occurs because database users which you see inside the database are “orphan“. Orphan users are those users for which there is no login id associated with this database user. This is true even if there is a login id that matches the user, since there is a GUID (security ID SID) that has to match as well. This can be easily understood by following picture,
This picture has been taken from Jonathan
User – means a user which you find inside a database
LOGIN – A login credentials which you see in security section of Server and use to connect with SQL Server.
Solution / FIX :
The solution is pretty simple, we need to make sure that SID of Login (Server Principal) matches with SID of database user.
To do so we have multiple Options
SOLUTION 1 : Maps an existing database user to a SQL Server login One by One
ALTER USER <Database_User_name> WITH LOGIN = <SQL_Server_LoginName>
Execute the above code, to make a mapping between database user and server login. In case you are using SQL Server 2005 SP1 and older versions then use the following SP
EXEC sp_change_users_login 'Update_One', '<DB_USER_NAME>','<SQL_Server_Login_NAME>';
IF you want to find all Orphan Users in current database, Database Users that are no link to SQL Server Login, use the following SQL statement.
SQL statement, which will list all Orphan Users
USE <YourDB_NAME> GO EXEC sp_change_users_login 'Report' GO
In case you have multiple Orphan users, then it’s pretty time consuming to map user to login one by one, to overcome this problem, we can do all at once using following TSQL statement
DECLARE @username varchar(50) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM master..sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers
this TSQL statement, will check all Logins, weather they have a similar name Database User or not, if it found, this will map the login with user.
NOTE - this Script might give you some errors, if a login doesn’t have a corresponding user, which should be fine and you can ignore those errors, as it has already fixed, what was matched.
Solution 2 :
Drop the Database User and create again and give necessary permission.