SQL Server

Error 15023 User already exists in the current database in SQL Server

In this post i will discuss about Error 15023 User already exists in the current database in SQL Server which can occur between SQL Server Login and Database User, This problem is also called Mismatched SID Problem

When an SQL Server login is created, the login is allocated both a name and a Security ID (SID).When a database user is created for the login, details of the SID for the login are entered into the database.

If the database is then backed up and restored onto second server, then database user still has the entry of SID of the previous server.When you create new Login on the second server it will have different SID. It means that SID of Database User is different from SID of Login. This is called Mismatched SID problem.

This is solved by two methods

1) Change the SID of the database user by the SID of the newly created Login created on the second server.This can be done via the ALTER USER command

2) Create the new login with the SID of the existing Database User

Demonstration

Now I am restoring a backup of a database which contains a user for a non-existent login. The name of my sample database is Mismatch database.

I right click on the database and then click Restore Database.

 

User already exists in the current database - Mismatched SID Problem

 

Choose the option from device then click the ellipses button and choose the path where the backup file is placed and hit Ok two times to restore the backup as shown below.

 

User already exists in the current database - Mismatched SID Problem

 

Now i create a login TestUser as shown below.

 

User already exists in the current database - Mismatched SID Problem

 

Try to create a user in the database for the login.
Note that this command fails also as a user with that name already exists.

 

User already exists in the current database - Mismatched SID Problem

 

Now question arises why did i create Login Name as  “TestUser” and Database User name as “TestUser”.This is because my sample database named Mismatch had Login Name        ” TestUser” as well as Database User name as “TestUser” in the first server.

From the command below i can view the database user present in the database
There is a TestUser already exists in the current database. Before running the sys.database_principals make sure Mismatch database must be selected as shown in the image below with highlighted red box.

 

User already exists in the current database - Mismatched SID Problem

 

Query the security IDs at both the server level and the database level to see the problem.The figure below shows that the security ID or SID of Login doesn’t match with the User of the database.

 

User already exists in the current database - Mismatched SID Problem

 

To map User of the database with the Login of the SQL Server is to correct the sid in the database. This can be done via the ALTER USER command as shown below.

 

User already exists in the current database - Mismatched SID Problem

 

Now i query the security IDs at both the server level and
the database level to see the outcome.

Note that the sids have been set to the value from the login, not the value from the database as shown below.

 

User already exists in the current database - Mismatched SID Problem

There is another option also available where you can set the value of the database SID to the Login.This can be done by creating the Login with the SID of the Database User as shown below.

User already exists in the current database - Mismatched SID Problem

 

Leave a Reply

Your email address will not be published.