SA0052 : Avoid using undocumented and deprecated stored procedures |
![]() |
Using deprecated or undocumented stored procedures in T-SQL code can lead to future compatibility issues.

In Microsoft SQL Server, using deprecated stored procedures or those that are undocumented may lead to significant problems as these procedures can be removed or altered in future SQL Server updates or releases. This can result in failures or unexpected behavior in your database applications.
For example:
1-- Example of using a deprecated stored procedure 2EXEC sp_addtype 'MyType', 'VARCHAR(100)';
In this example, sp_addtype is a deprecated stored procedure that could be removed in a future version of SQL Server. Such reliance may cause your scripts to break if the procedure is no longer available or its behavior changes.
-
Future SQL Server updates may remove deprecated stored procedures, breaking existing scripts.
-
Undocumented procedures are not guaranteed to maintain consistent behavior, leading to potential application inconsistencies.

This section provides guidance on how to address the use of deprecated or undocumented stored procedures in T-SQL code to maintain future compatibility with SQL Server updates.
Follow these steps to address the issue:
-
Identify and list all instances of deprecated or undocumented stored procedures in your existing T-SQL code. You can use tools like SQL Enlight or static code analysis features within SQL Server Management Studio (SSMS) to assist in this step.
-
Review the official Microsoft documentation to find recommended alternatives to the deprecated stored procedures. For example, for the deprecated sp_addtype, consider using CREATE TYPE.
-
Update your codebase to replace each deprecated or undocumented procedure with a supported alternative. Ensure that the new implementation maintains the existing functionality.
-
Thoroughly test the updated code to verify the changes do not introduce bugs or alter desired behavior. This involves running scripts and applications that depend on the modified code.
-
Plan for future maintenance by staying up to date with SQL Server documentation regarding deprecation and changes in subsequent versions.
For example:
1-- Example of corrected query replacing deprecated procedure 2CREATE TYPE MyType FROM VARCHAR(100);

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

SQL
1DECLARE @proc_name AS VARCHAR( 30 ) = 'sys.sp_addlogin'; 2 3-- sys.sp_addlogin procedure is deprecated. 4EXEC @proc_name 'Victoria' 5 , 'B1r12-36'; 6 7EXEC sp_addlogin 'Victoria' 8 , 'B1r12-36'; 9 10-- sys.sp_droplogin procedure is deprecated. 11EXECUTE sys.sp_droplogin 'Michael' 12 13-- sys.sp_bindrule procedure is deprecated. 14EXEC sp_bindrule 'today' 15 , 'HumanResources.Employee.HireDate' 16 17EXEC sys.sp_dropalias 'reportUser' |

Message | Line | Column | |
---|---|---|---|
1 | SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented. | 7 | 5 |
2 | SA0052 : Avoid using stored procedure [sp_droplogin] as it is either deprecated or undocumented. | 11 | 12 |
3 | SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented. | 14 | 5 |
4 | SA0052 : Avoid using stored procedure [sp_dropalias] as it is either deprecated or undocumented. | 17 | 9 |
