SQL Server error 15023 User already exists in current database

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