SA0052 : Avoid using undocumented and deprecated stored procedures |
![]() |
The topic describes the SA0052 analysis rule.

Avoid using undocumented and deprecated stored procedures

The rule checks the T-SQL code for calls to deprecated or undocumented stored procedures.
Deprecated procedures can be removed in a future version of Microsoft SQL Server while undocumented ones can be removed or changed even in the next release of update or service pack.

Avoid using this procedures in new development work, and plan to modify applications that currently use them.

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 ); 2 3SET @proc_name = 'sys.sp_addlogin'; 4 5 6-- sys.sp_addlogin procedure is deprecated. 7EXEC @proc_name 'Victoria' 8 , 'B1r12-36'; 9 10EXEC sp_addlogin 'Victoria' 11 , 'B1r12-36'; 12 13EXECUTE @proc_name 'Michael' 14 , 'B548bmM%f6' 15 , 'AdventureWorks' 16 , 'us_english' 17 , 0x0123456789ABCDEF0123456789ABCDEF 18 19-- sys.sp_droplogin procedure is deprecated. 20EXECUTE sys.sp_droplogin 'Michael' 21 22EXECUTE sp_addlogin 'Michael' 23 , 'B548bmM%f6' 24 , 'AdventureWorks' 25 , 'us_english' 26 , 0x0123456789ABCDEF0123456789ABCDEF 27 28-- sys.sp_droplogin procedure is deprecated. 29EXEC sp_droplogin 'Michael' 30 31-- sys.sp_addlogin procedure is deprecated. 32EXECUTE sys.sp_addlogin 'Michael' 33 , 'B548bmM%f6' 34 , 'AdventureWorks' 35 , 'us_english' 36 , 0x0123456789ABCDEF0123456789ABCDEF 37 38EXECUTE dbo..MyStoredProcedure 68 39 , 'C' 40 , 'House'; 41 42EXECUTE dbo..MyStoredProcedure @p1 = DEFAULT 43 , @p2 = 'D'; 44 45EXECUTE dbo.ProcTest DEFAULT 46 , @p3 = 'Local' 47 , @p2 = 'E'; 48 49DECLARE @result AS INT; 50 51SET @result = 0; 52 53-- sys.sp_bindrule procedure is deprecated. 54EXEC @result = sp_bindrule 'today' 55 , 'HumanResources.Employee.HireDate' 56 57EXEC @result = sp_bindrule @rulename = 'today' 58 , @objname = 'HumanResources.Employee.HireDate' 59 60EXEC sp_bindrule 'today' 61 , 'HumanResources.Employee.HireDate' 62 63PRINT @result; 64 65EXEC sp_addlinkedserver @server = 'ORACLE' 66 , @srvproduct = 'Oracle' 67 , @provider = 'OraOLEDB.Oracle' 68 , @datasrc = 'ORACLE10'; 69 70EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ORACLE' 71 , @useself = 'false' 72 , @locallogin = NULL 73 , @rmtuser = 'scott' 74 , @rmtpassword = 'tiger'; 75 76EXEC sp_serveroption 'ORACLE' 77 , 'rpc out' 78 , true; 79 80-- Execute several statements on the linked Oracle server. 81 82EXEC( 'SELECT * FROM scott.emp' ) AT ORACLE; 83 84EXEC( 'SELECT * FROM scott.emp WHERE MGR = ?' 85 , 7902 ) AT ORACLE; 86 87DECLARE @res AS INT; 88 89SET @res = 7902; 90 91DECLARE @proc_name2 AS VARCHAR( 30 ); 92 93SET @proc_name2 = 'sys.sp_addlogin'; 94 95-- sys.sp_addlogin procedure is deprecated. 96EXEC @res = @proc_name2 @loginame = 'Victoria' 97 , @passwd = 'B1r12-36'; 98 99EXEC 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_addlogin] as it is either deprecated or undocumented. | 10 | 5 |
3 | SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented. | 13 | 8 |
4 | SA0052 : Avoid using stored procedure [sp_droplogin] as it is either deprecated or undocumented. | 20 | 12 |
5 | SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented. | 22 | 8 |
6 | SA0052 : Avoid using stored procedure [sp_droplogin] as it is either deprecated or undocumented. | 29 | 5 |
7 | SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented. | 32 | 12 |
8 | SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented. | 54 | 15 |
9 | SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented. | 57 | 15 |
10 | SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented. | 60 | 5 |
… |
