Click or drag to resize

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.

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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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';

Analysis Results

 MessageLineColumn
1SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented.75
2SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented.105
3SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented.138
4SA0052 : Avoid using stored procedure [sp_droplogin] as it is either deprecated or undocumented.2012
5SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented.228
6SA0052 : Avoid using stored procedure [sp_droplogin] as it is either deprecated or undocumented.295
7SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented.3212
8SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented.5415
9SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented.5715
10SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented.605
...
See Also

Other Resources