SA0052 : Avoid using undocumented and deprecated stored procedures

The topic describes the SA0052 analysis rule.

Message

Avoid using undocumented and deprecated stored procedures

Description

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.

How to fix

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

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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'
Analysis Results
  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
…
See Also

Other Resources

Analysis Rules