The SQL Server error 15023 User already exists in current database occurs when a databases is restored from another instance. The database users aren’t mapped to the corresponding logins at the instance where it is restored and are termed as orphaned users. When one tries to login to the restored database with an orphan user we get message as “login failed for <user>”. The fix is very simple.
Before we fix it, we need to get the list of orphan users. In order to replicate the issue, create a database user and then drop the login it is mapped too. Execute the below query to get it.
-- Query 1: get the list of orphaned users (will b depricated in future) sp_change_users_login 'Report' -- Query 2: get the list of orphan users ( the new way ) 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')
The above two queries return all database users which doesn’t map to any server login. The output is shown below.
Execute the below query to map the database user to the server login.
-- Query 1: sp_change_users_login 'Update_one',<username>.<loginname> -- will be deprecated in future EXECUTE sp_change_users_login 'Update_one','login1','login1' -- Query 2: the new way ALTER USER login1 WITH LOGIN = login1
Query 1 uses the sp_change_users_login to map the user to a login with same name. This procedure will be deprecated in future. To get more on it refer to http://technet.microsoft.com/en-us/library/ms174378(v=sql.110).aspx
Query 2 uses the ALTER USER method to map the user to login.
The above queries will fix one user at a time. In order to fix all orphan user in a database, execute the below query.
-- fix all orphan users in database -- where username=loginname DECLARE @orphanuser varchar(50) DECLARE Fix_orphan_user CURSOR FOR 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') OPEN Fix_orphan_user FETCH NEXT FROM Fix_orphan_user INTO @orphanuser WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE('ALTER USER ' + @orphanuser + ' WITH LOGIN = ' + @orphanuser) FETCH NEXT FROM Fix_orphan_user INTO @orphanuser END CLOSE Fix_orphan_user DEALLOCATE Fix_orphan_user
The above iterates through all the orphan users and maps them to login with same name.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Excellent!!
thanks a ton. helped me a lot…
cheerooooooo
Thanks a lot 👍 its working.
GREAT!!!!