SQL server error 15023 surfaces after a database restoration on a different server. When a user mapping is not mapped correctly, you will experience such Database error.

In this context, we shall look into the cause of this SQL server error and how best to solve it.

Brief information about Microsoft SQL Server Error 15023

It is important to do a regular backup of your SQL server. This is because when any mishap happens, you will be able to do a restore process. So in some cases, the user mapping just does not seems to work correctly after you try to restore a database on a different server.

To map a user to a database, you can navigate through and implement the actions,  "Security - Logins - right click some user - Properties - User Mapping - Select DB - set as db_owner" settings and finally save changes by clicking "ok".

What triggers SQL Server Error 15023?

The process of restoring a database does not actually restores the master databases where Data such as "Logins" are held but only reinstates the user database.

We do expect that the backup of a database to work the same way as it were on the original server but it is important to know that the permissions have been altered and thus the Login doesn't work out. This is as a result of some conflicts or "orphaned" in the Security identification numbers (SID) in the sysusers table.

How we solved SQL Server Error 15023

To fix this SQL error, simply follow the following steps;

i. To begin, in the Query Analyzer, run the following T-SQL Query to return all the default users in the database;

USE YourDB
GO
EXEC sp_change_users_login ‘Report’
GO

Next, we use the "Auto_Fix" feature to link Login with the username to create the user in the new SQL server instance if it does not exist. Then  the current username and password of the user is used in this case. The function of Auto-Fix is to connect a user entry in the sysusers table in the new Database to a Login of same name as in the sysxlogins environment.

To do this, use the query below;

USE YourDB
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Username’, NULL, ‘Password’
GO

In order to link the Login to the username, we make an ‘Update_One’ action to link a particular user in the current database to login. In this case the the Login must already be available with the password being declared "NULL" while user and login specified.

Use the T-SQL Query below;

USE YourDB
GO
EXEC sp_change_users_login ‘update_one’, ‘ColdFusion’, ‘ColdFusion’
GO

ii. Finally, you should drop the the user since the login account has permission to drop other users in the database. To do this , use the T-SQL query below;

USE YourDB
GO
EXEC sp_dropuser ‘ColdFusion’
GO

Now you can create the same user in the database without any error.

Need support in fixing SQL related errors? Reach us Today.