DatabaseProcApplicationCreatedLinks
sybsystemprocssp_modify_resource_limit  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_modify_resource_limit"
6     **
7     ** 17231, "No login with the specified name exists."
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17261, "Only the System Administrator (SA) may execute this procedure."
10    ** 18182, "Timerange name must be non-NULL."
11    ** 18199, "Unknown time range name '%1!'."
12    ** 18202, "At least one of the login or application name must be non-NULL."
13    ** 18203, "Limit type must be non-NULL."
14    ** 18204, "Unknown limit type '%1!'."
15    ** 18206, "Illegal limit value %1!. Value must be non-negative."
16    ** 18207, "Illegal action %1!."
17    ** 18208, "Illegal enforcement-time value %1! for this limit type."
18    ** 18209, "Illegal scope value %1! for this limit type."
19    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
20    ** 18776, "Please also run stored procedure '%1!' on the companion server '%2!'."
21    ** 18779, "Unable to find the time range '%1!' with id '%2!' in systimeranges."
22    ** 18781, "Unable to find a limit type with name '%1!' and id '%2!'."
23    ** 
24    */
25    
26    /* 
27    ** IMPORTANT: Please read the following instructions before
28    **   making changes to this stored procedure.
29    **
30    **	To make this stored procedure compatible with High Availability (HA),
31    **	changes to certain system tables must be propagated 
32    **	to the companion server under some conditions.
33    **	The tables include (but are not limited to):
34    **		syslogins, sysservers, sysattributes, systimeranges,
35    **		sysresourcelimits, sysalternates, sysdatabases,
36    **		syslanguages, sysremotelogins, sysloginroles,
37    **		sysalternates (master DB only), systypes (master DB only),
38    **		sysusers (master DB only), sysprotects (master DB only)
39    **	please refer to the HA documentation for detail.
40    **
41    **	Here is what you need to do: 
42    **	For each insert/update/delete statement, add three sections to
43    **	-- start HA transaction prior to the statement
44    **	-- add the statement
45    **	-- add HA synchronization code to propagate the change to the companion
46    **
47    **	For example, if you are adding 
48    **		insert master.dbo.syslogins ......
49    **	the code should look like:
50    **	1. Before that SQL statement:
51    **		
52    **	2. Now, the SQL statement:
53    **		insert master.dbo.syslogins ......
54    **	3. Add a HA synchronization section right after the SQL statement:
55    **		
56    **
57    **	You may need to do similar change for each built-in function you
58    **	want to add.
59    **
60    **	Finally, add a separate part at a place where it can not
61    **	be reached by the normal execution path:
62    **	clean_all:
63    **		
64    **		return (1)
65    */
66    
67    create or replace procedure sp_modify_resource_limit
68        @name varchar(255), /* login to which limit applies */
69        @appname varchar(255), /* application to which limit applies */
70        @rangename varchar(255), /* range during which limit is enforced */
71        @limittype varchar(30), /* what's being limited */
72        @limitvalue int = NULL, /* upper-bound value for limit */
73        @enforced int, /* before or during execution */
74        @action int = NULL, /* what to do if limit is violated */
75        @scope int /* scope of limit */
76    as
77    
78        declare @limitid smallint
79        declare @rangeid smallint
80        declare @enforced_arg int
81        declare @scope_arg int
82        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
83        declare @retstat int
84        declare @nullarg char(1)
85        declare @dummy int
86        declare @status int
87        declare @gp_enabled int
88    
89    
90    
91        select @HA_CERTIFIED = 0
92    
93    
94    
95    
96        /* check to see if we are using HA specific SP for a HA enabled server */
97        exec @retstat = sp_ha_check_certified 'sp_modify_resource_limit', @HA_CERTIFIED
98        if (@retstat != 0)
99            return (1)
100   
101       /* 
102       ** If granular permissions is not enabled then sa_role is required.
103       ** If granular permissions is enabled then permission manage 'resource limit' is
104       ** required.  proc_role and proc_auditperm will also do auditing
105       ** if required. Both will also print error message if required.
106       */
107   
108       select @nullarg = NULL
109       execute @status = sp_aux_checkroleperm "sa_role", "manage resource limit",
110           @nullarg, @gp_enabled output
111   
112       /* For Auditing */
113       if (@gp_enabled = 0)
114       begin
115           if (proc_role("sa_role") = 0)
116               return (1)
117       end
118       else
119       begin
120           select @dummy = proc_auditperm("manage resource limit", @status)
121       end
122   
123       if (@status != 0)
124           return (1)
125   
126       if @@trancount > 0
127       begin
128           /*
129           ** 17260, "Can't run %1! from within a transaction." 
130           */
131           raiserror 17260, "sp_modify_resource_limit"
132           return (1)
133       end
134   
135       if ((@name is null) and (@appname is null))
136       begin
137           /*
138           ** 18202, "At least one of the login or application name must be non-NULL."
139           */
140           raiserror 18202
141           return (1)
142       end
143   
144       /* Was a valid user name specified? */
145       if ((@name is not null) and not exists
146                   (select * from master.dbo.syslogins where name = @name and
147                       ((status & 512) != 512))) /* not LOGIN PROFILE */
148       begin
149           /*
150           ** 17231, "No login with the specified name exists."
151           */
152           raiserror 17231
153           return (1)
154       end
155   
156       /* Was a valid range name specified? */
157   
158       select @rangename = rtrim(@rangename)
159   
160       if (@rangename is null)
161       begin
162           /*
163           ** 18182, "Timerange name must be non-NULL."
164           */
165           raiserror 18182
166           return (1)
167       end
168       else if not exists
169               (select * from master.dbo.systimeranges where name = @rangename)
170       begin
171           /*
172           ** 18199, "Unknown time range name '%1!'."
173           */
174           raiserror 18199, @rangename
175           return (1)
176       end
177   
178       /* Was a valid limit type specified? */
179       if (@limittype is null)
180       begin
181           /*
182           ** 18203, "Limit type must be non-NULL."
183           */
184           raiserror 18203
185           return (1)
186       end
187       else if not exists
188               (select * from master.dbo.spt_limit_types where name = @limittype)
189       begin
190           /*
191           ** 18204, "Unknown limit type '%1!'."
192           */
193           raiserror 18204, @limittype
194           return (1)
195       end
196   
197       /* Was a valid limit value specified? */
198       if ((@limitvalue is not null) and (@limitvalue <= 0))
199       begin
200           /*
201           ** 18206, "Illegal limit value %1!. Value must be non-negative."
202           */
203           raiserror 18206, @limitvalue
204           return (1)
205       end
206   
207       /* Was a valid action specified? */
208       if ((@action is not null) and ((@action < 1) or (@action > 4)))
209       begin
210           /*
211           ** 18207, "Illegal action %1!."
212           */
213           raiserror 18207, @action
214           return (1)
215       end
216   
217       /* Was a valid enforcement time specified? */
218       if (@enforced is null)
219       begin
220           select @enforced_arg = enforced from master.dbo.spt_limit_types where name = @limittype
221       end
222       else if (((@enforced & (select enforced from master.dbo.spt_limit_types where name = @limittype)) != @enforced) or (@enforced = 0))
223       begin
224           /*
225           ** 18208, "Illegal enforcement-time value %1! for this limit type."
226           */
227           raiserror 18208, @enforced
228           return (1)
229       end
230       else
231           select @enforced_arg = @enforced
232   
233       /* Was a valid scope specified? */
234       if (@scope is NULL)
235       begin
236           select @scope_arg = scope from master.dbo.spt_limit_types where name = @limittype
237       end
238       else if (((@scope & (select scope from master.dbo.spt_limit_types where name = @limittype)) != @scope) or (@scope = 0))
239       begin
240           /*
241           ** 18209, "Illegal scope value %1! for this limit type."
242           */
243           raiserror 18209, @scope
244           return (1)
245       end
246       else
247           select @scope_arg = @scope
248   
249       select @limitid = id from master.dbo.spt_limit_types where name = @limittype
250   
251       select @rangeid = id from master.dbo.systimeranges where name = @rangename
252   
253       if (select count(*) from master.dbo.sysresourcelimits
254               where
255                   ((((name = @name) and
256                               (appname = @appname)) or
257                           ((name = @name) and
258                               (@appname is null)) or
259                           ((@name is null) and
260                               (appname = @appname))) and
261                       (rangeid = @rangeid) and
262                       (limitid = @limitid) and
263                       (enforced = @enforced_arg) and
264                       (scope = @scope_arg))
265               ) = 0
266       begin
267           /*
268           ** 18220, "No such limit found in sysresourcelimits."
269           */
270           raiserror 18220
271           return (1)
272       end
273   
274   
275   
276       /* Modify the limit value */
277       if (@limitvalue is not null)
278       begin
279           update master.dbo.sysresourcelimits
280           set limitvalue = @limitvalue
281           where
282               ((((name = @name) and
283                           (appname = @appname)) or
284                       ((name = @name) and
285                           (@appname is null)) or
286                       ((@name is null) and
287                           (appname = @appname))) and
288                   (rangeid = @rangeid) and
289                   (limitid = @limitid) and
290                   (enforced = @enforced_arg) and
291                   (scope = @scope_arg))
292   
293   
294       end
295   
296       /* Modify the action */
297       if (@action is not null)
298       begin
299           update master.dbo.sysresourcelimits
300           set action = @action
301           where
302               ((((name = @name) and
303                           (appname = @appname)) or
304                       ((name = @name) and
305                           (@appname is null)) or
306                       ((@name is null) and
307                           (appname = @appname))) and
308                   (rangeid = @rangeid) and
309                   (limitid = @limitid) and
310                   (enforced = @enforced_arg) and
311                   (scope = @scope_arg))
312   
313   
314       end
315   
316       dbcc recachelimits
317   
318   
319   
320       return (0)
321   
322   clean_all:
323   
324   
325   
326       return (1)
327   
328   


exec sp_procxmode 'sp_modify_resource_limit', 'AnyMode'
go

Grant Execute on sp_modify_resource_limit to public
go
DEFECTS
 MURC 6 Unreachable Code 322
 MURC 6 Unreachable Code 326
 MTYP 4 Assignment type mismatch action: tinyint = int 300
 QCSC 4 Costly 'select count()', use 'exists()' 253
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 263
 QTYP 4 Comparison type mismatch tinyint = int 263
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 264
 QTYP 4 Comparison type mismatch tinyint = int 264
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 290
 QTYP 4 Comparison type mismatch tinyint = int 290
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 291
 QTYP 4 Comparison type mismatch tinyint = int 291
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 310
 QTYP 4 Comparison type mismatch tinyint = int 310
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 311
 QTYP 4 Comparison type mismatch tinyint = int 311
 TNOI 4 Table with no index master..spt_limit_types master..spt_limit_types
 TNOU 4 Table with no unique index master..sysresourcelimits master..sysresourcelimits
 TNOU 4 Table with no unique index master..systimeranges master..systimeranges
 MAW1 3 Warning message on %name% master..spt_limit_types.id: Warning message on spt_limit_types 249
 MAW1 3 Warning message on %name% master..systimeranges.id: Warning message on systimeranges 251
 MGTP 3 Grant to public master..spt_limit_types  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysresourcelimits  
 MGTP 3 Grant to public master..systimeranges  
 MGTP 3 Grant to public sybsystemprocs..sp_modify_resource_limit  
 MNER 3 No Error Check should check @@error after update 279
 MNER 3 No Error Check should check @@error after update 299
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 302
 MUCO 3 Useless Code Useless Brackets 320
 MUCO 3 Useless Code Useless Brackets 326
 MUOT 3 Updates outside transaction 299
 QAFM 3 Var Assignment from potentially many rows 220
 QAFM 3 Var Assignment from potentially many rows 236
 QAFM 3 Var Assignment from potentially many rows 249
 QAFM 3 Var Assignment from potentially many rows 251
 QPNC 3 No column in condition 258
 QPNC 3 No column in condition 259
 QPNC 3 No column in condition 285
 QPNC 3 No column in condition 286
 QPNC 3 No column in condition 305
 QPNC 3 No column in condition 306
 QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 261
 QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 262
 QSWV 3 Sarg with variable @enforced_arg, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 263
 QSWV 3 Sarg with variable @scope_arg, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 264
 QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 288
 QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 289
 QSWV 3 Sarg with variable @enforced_arg, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 290
 QSWV 3 Sarg with variable @scope_arg, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 291
 QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 308
 QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 309
 QSWV 3 Sarg with variable @enforced_arg, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 310
 QSWV 3 Sarg with variable @scope_arg, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 311
 VNRD 3 Variable is not read @dummy 120
 MSUB 2 Subquery Marker 146
 MSUB 2 Subquery Marker 169
 MSUB 2 Subquery Marker 188
 MSUB 2 Subquery Marker 222
 MSUB 2 Subquery Marker 238
 MSUB 2 Subquery Marker 253
 MTR1 2 Metrics: Comments Ratio Comments: 49% 67
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 38dec - 14exi + 2 67
 MTR3 2 Metrics: Query Complexity Complexity: 155 67

DATA PROPAGATION detailed
ColumnWritten To
@actionsysresourcelimits.action   sp_drop_time_range_rset_001.action
@limitvaluesysresourcelimits.limitvalue   sp_drop_time_range_rset_001.limitvalue

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syslogins (1)  
reads table master..systimeranges (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
reads table master..spt_limit_types (1)  
read_writes table master..sysresourcelimits (1)