DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setsuspect_threshold  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** 17260, "Can't run %1! from within a transaction." 
6     ** 17421, "No such database -- run sp_helpdb to list databases."
7     ** 17422, "The 'master' database's options can not be changed."
8     ** 17428, "You must be in the 'master' database in order to change
9     **	database options."                                
10    ** 18523, "'%1!': Not allowed for System databases."
11    ** 18524, "'%1!': Permission denied. This operation requires System Administrator (sa_role) role."                                
12    ** 18526, "'%1!': The new values will become effective during the next recovery of the database."
13    ** 18528, sp_setsuspect_threshold: Suspect Threshold value '%1!' is not valid
14    ** 18529, sp_setsuspect_threshold: Not allowed because '%1!' has database level suspect granularity 
15    */
16    
17    create or replace procedure sp_setsuspect_threshold
18        @dbname varchar(255) = NULL,
19        @threshold int = NULL
20    as
21        declare @dbid int, /* dbid of the database */
22            @attrib_id int,
23            @action int,
24            @object_type varchar(2),
25            @msg varchar(1024),
26            @cur_threshold int,
27            @cfg_threshold int,
28            @default_threshold int,
29            @sptlang int,
30            @class int,
31            @whichone int, /* which language? */
32            @cfg_susgran int,
33            @sysdbid int,
34            @dummy int,
35            @status int,
36            @gp_enabled int
37    
38        select @attrib_id = 1 /* attribute is SUSPECT THRESHOLD */
39        select @object_type = 'D'
40        select @default_threshold = 20
41        select @cur_threshold = 20
42        select @cfg_threshold = 20
43        select @class = 10
44        select @cfg_susgran = 0
45    
46    
47        if @@trancount = 0
48        begin
49            set chained off
50        end
51    
52        set transaction isolation level 1
53    
54        select @sptlang = @@langid, @whichone = 0
55    
56        if @@langid != 0
57        begin
58            if not exists (
59                    select * from master.dbo.sysmessages where error
60                        between 17050 and 17069
61                        and langid = @@langid)
62                select @sptlang = 0
63        end
64    
65    
66        /*
67        **  If no @dbname given, assume the current database
68        */
69    
70        if @dbname is null
71            select @dbname = db_name()
72    
73        /*
74        **  Verify the database name and get the @dbid 
75        */
76        select @dbid = db_id(@dbname)
77    
78        /*
79        **  If @dbname not found, say so.
80        */
81        if @dbid is NULL
82        begin
83            /*
84            ** 17421, "No such database -- run sp_helpdb to list databases."
85            */
86            exec sp_getmessage 17421, @msg output
87            print "sp_setsuspect_threshold: '%1!'", @msg
88            return (1)
89        end
90    
91        if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "sybsystemdb")
92        begin
93            /*
94            ** 18523, "%1!:Not allowed for System databases."
95            */
96            raiserror 18523, "sp_setsuspect_threshold"
97            return (1)
98        end
99    
100       /*
101       ** If only dbname is provided, then display the setting
102       */
103   
104       if @threshold is NULL
105       begin
106   
107           select @cur_threshold = int_value,
108               @cfg_threshold = object_info2
109           from master.dbo.sysattributes
110           where class = @class AND
111               attribute = @attrib_id AND
112               object_type = @object_type AND
113               object = @dbid
114   
115           select "DB Name" = @dbname,
116               "Cur. Suspect threshold" = @cur_threshold,
117               "Cfg. Suspect threshold" = @cfg_threshold
118           return (0)
119       end
120   
121       /*
122       **  If granular permissions is not enabled then only the Accounts with SA role 
123       **  can execute it.
124       **  If granular permissions is enabled then the user must have 'own database'
125       **  permission.
126       **  Both proc_role and proc_auditperm will print error message if needed and
127       **  do auditing.
128       */
129   
130       execute @status = sp_aux_checkroleperm "sa_role",
131           "own database", @dbname, @gp_enabled output
132       if (@status != 0)
133       begin
134           /*
135           ** Send apropriate audit records.
136           */
137           if (@gp_enabled = 0)
138           begin
139               select @dummy = proc_role("sa_role")
140               /*
141               ** 18524, "%1!:Permission denied. This operation requires 
142               ** System Administrator (sa_role) role."
143               */
144               raiserror 18524, "sp_setsuspect_threshold"
145           end
146           else
147           begin
148               select @dummy = proc_auditperm("own database",
149                       @status, @dbname)
150           end
151           return (1)
152       end
153   
154       /* For Auditing */
155       if (@gp_enabled = 0)
156       begin
157           select @dummy = proc_role("sa_role")
158       end
159       else
160       begin
161           if (@status = 0)
162               select @dummy = proc_auditperm("own database", @status,
163                       @dbname)
164       end
165   
166       if db_name() != "master"
167       begin
168           /*
169           ** 17428, "You must be in the 'master' database in order to change database options."                                
170           */
171           exec sp_getmessage 17428, @msg output
172           print "sp_setsuspect_threshold: %1!", @msg
173           return (1)
174       end
175   
176       /*
177       **  If we're in a transaction, disallow this since it might make recovery
178       **  impossible.
179       */
180       if @@trancount > 0
181       begin
182           /*
183           ** 17260, "Can't run %1! from within a transaction." 
184           */
185           raiserror 17260, "sp_setsuspect_threshold"
186           return (1)
187       end
188       else
189       begin
190           set chained off
191       end
192   
193       set transaction isolation level 1
194   
195       /*
196       ** Check to see that the input params are correct and then hook up with
197       ** Sysattributes table to enter data.
198       */
199   
200       if (@threshold < 0)
201       BEGIN
202           /*
203           ** 18528, sp_setsuspect_threshold: Suspect Threshold value '%1!' is not valid
204           */
205           raiserror 18528, @threshold
206           return (1)
207       END
208   
209       /* get the configured suspect granularity value */
210       select
211           @cfg_susgran = object_info2
212       from master.dbo.sysattributes
213       where class = @class AND
214           attribute = 0 AND
215           object_type = @object_type AND
216           object = @dbid
217   
218       /* if configured suspect granularity is database, then return error */
219       if (@cfg_susgran = 0)
220       begin
221           /*
222           ** 18529, sp_setsuspect_threshold: Not allowed because '%1!' has database level suspect granularity 
223           */
224           raiserror 18529, @dbname
225           return (1)
226       end
227   
228   
229       /*
230       ** if an entry already exists for this database then update the entry,
231       ** otherwise insert a new row for this database.
232       ** When updating, change only the config value (object_info2) and not the
233       ** current value (int_value). The config value will take effect only
234       ** during the next boot/load time recovery of the database, during which
235       ** time the current value will be updated to the config value.
236       */
237       IF exists (select * from master.dbo.sysattributes where
238                   class = @class AND
239                   attribute = @attrib_id AND
240                   object_type = @object_type AND
241                   object = @dbid)
242       begin
243           update master.dbo.sysattributes
244           set object_info2 = @threshold
245           where class = @class AND
246               attribute = @attrib_id AND
247               object_type = @object_type AND
248               object = @dbid
249       end
250       else
251       begin
252           insert master.dbo.sysattributes
253           (class, attribute, object_type, object, int_value, object_info2)
254           values (@class, @attrib_id, @object_type, @dbid,
255               @default_threshold, @threshold)
256       end
257   
258   
259   
260       /*
261       ** Display the setting 
262       */
263   
264       select "Database Name" = @dbname,
265           "Cur. Suspect Threshold" = int_value,
266           "Cfg. Suspect Threshold" = object_info2
267       from master.dbo.sysattributes
268       where class = @class AND
269           attribute = @attrib_id AND
270           object_type = @object_type AND
271           object = @dbid
272   
273       /*
274       ** 18526, "'%1!': The new values will become effective during the next recovery of the database %2!."
275       */
276       exec sp_getmessage 18526, @msg output
277       print @msg, "sp_setsuspect_threshold", @dbname
278       return (0)
279   


exec sp_procxmode 'sp_setsuspect_threshold', 'AnyMode'
go

Grant Execute on sp_setsuspect_threshold to public
go
RESULT SETS
sp_setsuspect_threshold_rset_002
sp_setsuspect_threshold_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch attribute: smallint = int 254
 MTYP 4 Assignment type mismatch class: smallint = int 254
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 61
 QTYP 4 Comparison type mismatch smallint = int 61
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 110
 QTYP 4 Comparison type mismatch smallint = int 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 111
 QTYP 4 Comparison type mismatch smallint = int 111
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 213
 QTYP 4 Comparison type mismatch smallint = int 213
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 214
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 238
 QTYP 4 Comparison type mismatch smallint = int 238
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 239
 QTYP 4 Comparison type mismatch smallint = int 239
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 245
 QTYP 4 Comparison type mismatch smallint = int 245
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 246
 QTYP 4 Comparison type mismatch smallint = int 246
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 268
 QTYP 4 Comparison type mismatch smallint = int 268
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 269
 QTYP 4 Comparison type mismatch smallint = int 269
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setsuspect_threshold  
 MNER 3 No Error Check should check return value of exec 86
 MNER 3 No Error Check should check return value of exec 171
 MNER 3 No Error Check should check @@error after update 243
 MNER 3 No Error Check should check @@error after insert 252
 MNER 3 No Error Check should check return value of exec 276
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 278
 MUOT 3 Updates outside transaction 252
 MUPK 3 Update column which is part of a PK or unique index object_info2 244
 QAFM 3 Var Assignment from potentially many rows 107
 QAFM 3 Var Assignment from potentially many rows 210
 QCRS 3 Conditional Result Set 115
 QISO 3 Set isolation level 52
 QISO 3 Set isolation level 193
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 253
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
59
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
110
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
213
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
238
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
245
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
268
 VNRD 3 Variable is not read @whichone 54
 VNRD 3 Variable is not read @sptlang 62
 VNRD 3 Variable is not read @dummy 162
 VUNU 3 Variable is not used @action 23
 VUNU 3 Variable is not used @sysdbid 33
 MRST 2 Result Set Marker 115
 MRST 2 Result Set Marker 264
 MSUB 2 Subquery Marker 58
 MSUB 2 Subquery Marker 237
 MTR1 2 Metrics: Comments Ratio Comments: 42% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 28dec - 9exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 109 17

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesysattributes.object   °.object_info1   °.object_info3   °.char_value   sysremotelogins.remoteserverid   sysservers.srvid   sysattributes.object_info1   °.object_info3   °.int_value   °.char_value  
sp_dropdevice_rset_001.device sp_checknames_rset_006.remoteserverid sp_checknames_rset_007.srvid sp_checknames_rset_008.srvid sp_dbrecovery_order_rset_001.Database Name °.Database id sp_displayroles_rset_001.Role Name sp_displayroles_rset_002.Role Name °.Grantee sp_displayroles_rset_003.Role Name
sp_displayroles_rset_004.Role Name sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.Object °.status sp_forceonline_object_rset_002.Object °.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Object °.Access
sp_listsuspect_page_rset_001.Object °.Access sp_makesuspect_obj_rset_001.Obj °.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Obj
°.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Obj °.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_memlog_rset_001.dumps_per_file sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode
sp_setsuspect_threshold_rset_001.DB Name sp_setsuspect_threshold_rset_002.Database Name sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference
@thresholdsysattributes.object_info2   °.char_value   sysattributes.object_info2   °.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.Index °.status sp_forceonline_object_rset_002.Index
°.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Index °.Access sp_listsuspect_page_rset_001.Index °.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid
°.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name
sp_setsuspect_granularity_rset_001.Online mode sp_setsuspect_threshold_rset_001.Cfg. Suspect threshold sp_setsuspect_threshold_rset_002.Cfg. Suspect Threshold sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysmessages (1)  
writes table sybsystemprocs..sp_setsuspect_threshold_rset_001 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
writes table sybsystemprocs..sp_setsuspect_threshold_rset_002 
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages