Have you ever encountered the error message “Error 15023: User already exists in current database” while working with SQL Server? This error occurs when a user exists at the server level but is not associated with any database. In this blog post, we will discuss how to fix orphan users in SQL Server and resolve this error.

Method 1: Using T-SQL Queries

The first method involves using T-SQL queries to fix orphan users. Follow these steps:

1. Open Query Analyzer and run the following T-SQL query to get a list of all existing users in the database:

USE YourDB
EXEC sp_change_users_login 'Report'

2. Next, run the following T-SQL query to associate the login with the username. This will create the user in the SQL Server instance if it does not exist:

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

3. Finally, run the following T-SQL query to link the specified user in the current database to the login. The login must already exist:

USE YourDB
EXEC sp_change_users_login 'Update_One', 'ColdFusion', 'ColdFusion'

Method 2: Using Stored Procedures

If you have permission to drop other users, you can use the following stored procedures to fix orphan users:

Stored Procedure 1: spDBA_FixOrphanUsers

This stored procedure fixes all the orphan users in the database by mapping them to usernames that already exist on the server. It is useful when a user has been created at the server level but does not show up 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 Procedure 2: spDBA_FixOrphanUsersPassWord

This stored procedure fixes all the orphan users in the database by creating server-level users with the same password as the username. Make sure to change all the 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 Procedure 3: spDBA_DropOrphanUsers

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

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

By using either the T-SQL queries or the provided stored procedures, you can easily fix orphan users in SQL Server and resolve the “Error 15023: User already exists in current database” issue. Remember to choose the method that best suits your requirements and permissions.