Скачать или смотреть Resolving Invalid Data for UTF8-encoded Characters Error in SQL Server 2019

Learn how to fix the `Invalid Data for UTF8-encoded Characters` error in SQL Server 2019 by using the `ALTER USER` command instead of the deprecated procedure.
---
This video is based on the question https://stackoverflow.com/q/66460035/ asked by the user 'Jean Valjean' ( https://stackoverflow.com/u/2665524/ ) and on the answer https://stackoverflow.com/a/66460373/ provided by the user 'eshirvana' ( https://stackoverflow.com/u/1367454/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL 2019 : Invalid data for UTF8-encoded characters

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Resolving Invalid Data for UTF8-encoded Characters Error in SQL Server 2019

When working with SQL Server 2019, developers and database administrators may encounter various error messages that can disrupt their workflow. One such error is the Invalid data for UTF8-encoded characters, which can arise when attempting to execute certain commands. This guide will break down the causes of this issue and provide a step-by-step solution to rectify it.

Understanding the Problem

The error often occurs when running the following SQL command:

[[See Video to Reveal this Text or Code Snippet]]

Here’s the error message you may face:

[[See Video to Reveal this Text or Code Snippet]]

This indicates that your SQL Server, with UTF8 encoding enabled, is unable to process the data correctly, which results in the failure of the operation.

Why Does This Error Occur?

The root of the problem lies in the use of the deprecated sp_change_users_login procedure. As SQL Server versions progress, Microsoft transitions to more efficient and modern commands, leaving older procedures obsolete. The UTF8 encoding feature in SQL Server 2019 may further exacerbate issues with older commands, leading to complications.

Common Causes of the Error:

Usage of deprecated command (sp_change_users_login).

Issues with character encoding that arise when granting logins to database users.

Presence of orphaned users that the older command may fail to address properly.

The Solution: Using the ALTER USER Command

To bypass the issues associated with the deprecated procedure, it is recommended to use the modern ALTER USER command. This command is designed to handle user management more effectively in SQL Server 2019. Here’s how to implement this solution:

Step-by-Step Solution

Identify the User: Know the database user you want to modify. For example, if you're working with the user JEANC, ensure you have the correct credentials.

Change User Password: Use the ALTER USER command to change or reset the user password. Here is an example:

[[See Video to Reveal this Text or Code Snippet]]

Replace 'new_password' with a secure password of your choice.

Confirm Changes: After executing the command, ensure that the changes have been applied successfully by checking user permissions and logins.

Example Command Execution

Here’s how you might run the command in practice:

[[See Video to Reveal this Text or Code Snippet]]

After running this command, you can verify the user’s status and continue to manage user logins without encountering the UTF8 character error.

Conclusion

Dealing with SQL Server 2019 can sometimes be challenging, especially when it comes to using outdated procedures like sp_change_users_login. By transitioning to the modern ALTER USER command, you can effectively manage users and avoid errors related to UTF8 encoding. With this knowledge, you should now be equipped to handle orphaned users seamlessly in SQL Server 2019.

If you continue to experience issues, be sure to double-check your user permissions and database settings to ensure everything is configured correctly. Happy coding!