Resolving SQL Server Error 15023

When restoring a database to a different server, user mapping can cause SQL Server Error 15023 to occur. At Codeyo Genie, we’ve encountered various causes for this issue while performing SQL troubleshooting as part of our Server Management Services for web hosts and online service providers. Today, we’ll delve into the root cause of this error and provide recommended solutions.

Understanding SQL Server Error 15023

Backup and restore are fundamental processes in SQL Server. However, when restoring a database to a new server, user mapping might not work as expected. When attempting to connect a database to a user by navigating to:

Security >> Logins >> right click some user >> Properties >> User Mapping >> Select DB >> set as db_owner

After clicking “OK,” you might receive the following error :

How to fix SQL Server Error 15023

What Leads to SQL Server Error 15023?

When we backup and restore databases across SQL servers, the master database (which contains login information) is typically not restored. We only restore the user databases. While we expect the restored database to function like the backup, users with permissions in the database backup might be unable to access it. This problem is often caused by incorrect or ‘orphaned’ Security Identification Numbers (SIDs) in the sysusers table.

How to Resolve Error 15023 in SQL Server

Solution 1:

Let’s explore how our support engineers at Codeyo Genie fix this error:

We begin by running the following T-SQL query in Query Analyzer. This query returns all current users in the database.

USE YourDB
      GO
      EXEC sp_change_users_login 'Report'
      GO

We then use the following T-SQL query to link the login with the username. If the user doesn’t already exist, the ‘Auto_Fix’ attribute will create it.

USE YourDB
      GO
      EXEC sp_change_users_login 'Auto_Fix', 'Username', NULL, 'Password'
      GO

We also execute the following T-SQL query to link login with the username using ‘Update_One,’ which connects the chosen user to the login in the active database.

USE YourDB
        GO
        EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
        GO

If the login account has the authority to delete other users, we execute the following T-SQL query to drop the user:

USE YourDB
      GO
      EXEC sp_dropuser 'ColdFusion'
      GO

This successfully recreates the identical user in the database.

Solution 2:

For the ideal solution, we follow these steps:

Run the following T-SQL query in Query Analyzer to obtain a list of current users in the database:

USE YourDB
      GO
      EXEC sp_change_users_login 'Report'
      GO

Execute this T-SQL query to connect the username and login:

USE YourDB
      GO
      EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
      GO

Execute this T-SQL query to connect the username and login:

USE YourDB
      GO
      EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
      GO

If the login account has the authority to delete other users, execute the following T-SQL query to drop the user:

USE YourDB
      GO
      EXEC sp_dropuser 'ColdFusion'
      GO

Add the identical user back to the database without encountering any errors.

Stored Method (Procedure) 1:

The following stored procedure fixes all orphan users in the database by mapping them to existing usernames on the server. This procedure is useful when a user has been created at the server level but doesn’t appear as a user in the database.

CREATE PROCEDURE dbo.spDBA_FixOrphanUsers
      AS
      DECLARE @username VARCHAR(25)
      DECLARE GetOrphanUsers CURSOR
      FOR
      SELECT UserName = name
      FROM sysusers
      WHERE issqluser = 1
      AND (sid IS NOT NULL
      AND sid <> 0x0)
      AND SUSER_SNAME(sid) IS NULL
      ORDER BY name
      OPEN GetOrphanUsers
      FETCH NEXT
      FROM GetOrphanUsers
      INTO @username
      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF @username='dbo'
      EXEC sp_changedbowner 'sa'
      ELSE
      EXEC sp_change_users_login 'update_one', @username, @username
      FETCH NEXT
      FROM GetOrphanUsers
      INTO @username
      END
      CLOSE GetOrphanUsers
      DEALLOCATE GetOrphanUsers
      GO

Stored Method (Procedure) 2:

This stored procedure fixes orphan users by creating server-level users with the same password as the username. Note that you should change all passwords once the users are created.

CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord
      AS
      DECLARE @username VARCHAR(25)
      DECLARE @password VARCHAR(25)
      DECLARE GetOrphanUsers CURSOR
      FOR
      SELECT UserName = name
      FROM sysusers
      WHERE issqluser = 1
      AND (sid IS NOT NULL
      AND sid <> 0x0)
      AND SUSER_SNAME(sid) IS NULL
      ORDER BY name
      OPEN GetOrphanUsers
      FETCH NEXT
      FROM GetOrphanUsers
      INTO @username
      SET @password = @username
      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF @username='dbo'
      EXEC sp_changedbowner 'sa'
      ELSE
      EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password
      FETCH NEXT
      FROM GetOrphanUsers
      INTO @username
      END
      CLOSE GetOrphanUsers
      DEALLOCATE GetOrphanUsers
      GO

Stored Method (Procedure) 3:

This stored procedure drops all orphan users in the database. If you need any of those users, you can recreate them.

CREATE PROCEDURE dbo.spDBA_DropOrphanUsers
      AS
      DECLARE @username VARCHAR(25)
      DECLARE GetOrphanUsers CURSOR
      FOR
      SELECT UserName = name
      FROM sysusers
      WHERE issqluser = 1
      AND (sid IS NOT NULL
      AND sid <> 0x0)
      AND SUSER_SNAME(sid) IS NULL
      ORDER BY name
      OPEN GetOrphanUsers
      FETCH NEXT
      FROM GetOrphanUsers
      INTO @username
      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF @username='dbo'
      EXEC sp_changedbowner 'sa'
      ELSE
      EXEC sp_dropuser @username
      FETCH NEXT
      FROM GetOrphanUsers
      INTO @username
      END
      CLOSE GetOrphanUsers
      DEALLOCATE GetOrphanUsers
      GO

Solution 3:

To correct the issue, we need to obtain the list of orphan users. Create a database user and then delete the login it is mapped to in order to reproduce the problem. Run the following queries:

-- Query 1: Obtain the list of orphaned users who will soon be denied access.
sp_change_users_login 'Report'

-- Query 2: Obtain the list of orphaned users (new method)
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')

These queries provide a list of database users unrelated to any server login information.The results are shown below.

SQL Server error 15023 user already exists in current database

To link the database user to the server login, use the following queries:

-- Query 1: This method will be deprecated in the future
EXEC sp_change_users_login 'Update_one','login1','login1'

-- Query 2: The new way
ALTER USER login1 WITH LOGIN = login1

These queries link the user to the login using the respective methods.

Finally, you can fix all orphan users in the database by executing the following query:

-- Fix all orphan users in the database
  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

This query fixes all orphan users in the database by mapping them to the corresponding login.

Do you need further assistance in resolving SQL errors? We’re available 24/7 to help.