The User Mapping Fails on the SQL Server

NOTES:

• Advanced knowledge about the Microsoft SQL Server is usually required while working with the CUSTOMTOOLS Databases using the SQL Server Management Studio.
• For the reason above, the error investigation and/or fixing on the database issues that are not caused by CUSTOMTOOLS daily usage are considered as support work that is invoiced separately.

Error Description

When for example a CUSTOMTOOLS Database have been copied from a server to another, the user called ct_admin may exist in the target SQL Server and also in the CUSTOMTOOLS Database backup set restored. In that case, the SQL Server's ct_admin user does not have rights to the CUSTOMTOOLS Database restored from the backup set and the server's ct_admin user have to be mapped with the CUSTOMTOOLS Database.

In a case the ct_admin user already exist in the CUSTOMTOOLS Database, and you are about to map the SQL Server's ct_admin user to that database, you receive an error message shown in the following picture.

Solution

To solve the problem and to be able to map the server's ct_admin user with your restored CUSTOMTOOLS Database, you need to rename the ct_admin user that already exists in your CUSTOMTOOLS Database. Please refer to the following picture to see where to find the Rename command for the database specific user called ct_admin.

Renaming the existing user called ct_admin in the target database allows you to map the server's ct_admin user with the database. When the server's ct_admin user is being mapped with the database, the user entry called ct_admin appears again under the Security, Users node of your CUSTOMTOOLS database.

Once you have renamed the existing user in your database, you can map the SQL Server's ct_admin user to your database by referring to the following instructions and pictures.

1. Collapse the Databases node in your SQL Server Management Studio
2. Expand the Security, Logins node
3. Right-click the login called ct_admin and select Properties
4. Under the Select a page group, select the Server Roles page
   • Ensure that the public role is selected. Nothing else is needed.
     
5. Switch to the User Mapping page
   • From the Users mapped to this login options, select your CUSTOMTOOLS Database the ct_admin user was renamed earlied in these instructions and click the ... button associated to that database to select the desired schema. Select ct as the Default Schema.
     
   • While you still have you CUSTOMTOOLS Database selected from the Users mapped to this login options, select the following options from the Database role membership for the selected database (see the picture above):
     • db_owner
     • public
6. Click OK to save changes to the ct_admin login.

After the instructions above, you are able to login and use your CUSTOMTOOLS Database again using your ct_admin SQL Server user.

Alternative Solution

You can also use an SQL Query to perform the automatic fixing for your ct_admin user that already exists in your CUSTOMTOOLS Database. The following SQL Query performs the automatic fix.

EXEC sp_change_users_login 'Auto_Fix', 'ct_admin', NULL, 'ct_admin';

GO

For more information about the alternative solution above, please see the following article in the Microsoft Docs:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2017

Please find the solution above from under the Automatically mapping a user to a login, creating a new login if it is required topic.