SA0222 : The ALTER LOGIN WITH SET CREDENTIAL or ALTER LOGIN WITH NO CREDENTIAL syntax is deprecated
Introduction
Section titled “Introduction”Using the deprecated ALTER LOGIN with the SET CREDENTIAL option in T-SQL code may lead to compatibility issues in future SQL Server versions.
Description
Section titled “Description”Many versions of SQL Server have deprecated certain features, which are considered outdated and might be removed in future releases. The T-SQL syntax ALTER LOGIN WITH SET CREDENTIAL or NO CREDENTIAL is one such feature. Using deprecated features can cause unexpected behavior and complicate future upgrades.
For example:
-- Example of deprecated queryALTER LOGIN [userName] WITH SET CREDENTIAL = credentialName;This expression is problematic because it leverages a syntax no longer recommended for managing credentials. Continued use can lead to difficulties in maintaining the database as SQL Server evolves, forcing developers to revise existing scripts during migration or upgrade tasks.
-
Deprecated syntax can lead to code interruptions in future SQL Server versions.
-
Makes code maintenance harder due to reliance on obsolete practices.
How to fix
Section titled “How to fix”To resolve issues related to deprecated T-SQL syntax for managing credentials, use the recommended syntax with ALTER LOGIN ADD/DROP CREDENTIAL .
Follow these steps to address the issue:
1.Identify the instances in your scripts where ALTER LOGIN WITH SET CREDENTIAL or NO CREDENTIAL is used.
2.Replace the deprecated syntax with the appropriate ALTER LOGIN ADD CREDENTIAL statement to add a credential.
3.Use ALTER LOGIN DROP CREDENTIAL to remove a credential, if necessary.
For example:
-- Example of corrected queryALTER LOGIN [userName] ADD CREDENTIAL credentialName;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”ALTER LOGIN Joe2 WITH CREDENTIAL = Custodian04;ALTER LOGIN Joe2 WITH NO CREDENTIAL
ALTER LOGIN Joe2 ADD CREDENTIAL Custodian04;ALTER LOGIN Joe2 DROP CREDENTIAL Custodian04;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0222 : The ALTER LOGIN WITH SET CREDENTIAL or ALTER LOGIN WITH NO CREDENTIAL syntax is deprecated. | 1 | 17 |
| 2 | SA0222 : The ALTER LOGIN WITH SET CREDENTIAL or ALTER LOGIN WITH NO CREDENTIAL syntax is deprecated. | 2 | 17 |