DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_resource_limit  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_drop_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    ** 18199, "Unknown time range name '%1!'."
11    ** 18202, "At least one of the login or application name must be non-NULL."
12    ** 18204, "Unknown limit type '%1!'."
13    ** 18207, "Illegal action %1!."
14    ** 18209, "Illegal scope value %1! for this limit type."
15    ** 18215, "Resource limit dropped."
16    ** 18220, "No such limit found in sysresourcelimits."
17    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'"
18    ** 18776, "Please also run stored procedure '%1!' on the companion server '%2!'."
19    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
20    ** 18779, "Unable to find the time range '%1!' with id '%2!' in systimeranges.
21    ** 18781, "Unable to find a limit type with name '%1!' and id '%2!'."
22    */
23    
24    /* 
25    ** IMPORTANT: Please read the following instructions before
26    **   making changes to this stored procedure.
27    **
28    **	To make this stored procedure compatible with High Availability (HA),
29    **	changes to certain system tables must be propagated 
30    **	to the companion server under some conditions.
31    **	The tables include (but are not limited to):
32    **		syslogins, sysservers, sysattributes, systimeranges,
33    **		sysresourcelimits, sysalternates, sysdatabases,
34    **		syslanguages, sysremotelogins, sysloginroles,
35    **		sysalternates (master DB only), systypes (master DB only),
36    **		sysusers (master DB only), sysprotects (master DB only)
37    **	please refer to the HA documentation for detail.
38    **
39    **	Here is what you need to do: 
40    **	For each insert/update/delete statement, add three sections to
41    **	-- start HA transaction prior to the statement
42    **	-- add the statement
43    **	-- add HA synchronization code to propagate the change to the companion
44    **
45    **	For example, if you are adding 
46    **		insert master.dbo.syslogins ......
47    **	the code should look like:
48    **	1. Before that SQL statement:
49    **		
50    **	2. Now, the SQL statement:
51    **		insert master.dbo.syslogins ......
52    **	3. Add a HA synchronization section right after the SQL statement:
53    **		
54    **
55    **	You may need to do similar change for each built-in function you
56    **	want to add.
57    **
58    **	Finally, add a separate part at a place where it can not
59    **	be reached by the normal execution path:
60    **	clean_all:
61    **		
62    **		return (1)
63    */
64    
65    create or replace procedure sp_drop_resource_limit
66        @name varchar(255), /* login to which limit applies */
67        @appname varchar(255), /* application to which limit applies */
68        @rangename varchar(255) = NULL, /* timerange to which limit applies */
69        @limittype varchar(255) = NULL, /* what's being limited */
70        @enforced int = NULL, /* before or during execution */
71        @action int = NULL, /* what to do upon violation */
72        @scope int = NULL /* scope of limit */
73    as
74    
75        declare @limitid smallint
76        declare @rangeid smallint
77        declare @msg varchar(1024)
78        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
79        declare @retstat int
80        declare @dummy int
81        declare @nullarg varchar(1) /* Used to pass NULL as argument */
82        declare @gp_enabled int
83    
84    
85        select @HA_CERTIFIED = 0
86    
87        select @nullarg = NULL
88        execute @retstat = sp_aux_checkroleperm "sa_role", "manage resource limit",
89            @nullarg, @gp_enabled output
90    
91        if (@gp_enabled = 0)
92        begin
93            if (proc_role("sa_role") = 0) return 1
94        end
95        else
96        begin
97            select @dummy = proc_auditperm("manage resource limit",
98                    @retstat)
99        end
100   
101       if (@retstat != 0)
102       begin
103           return (1)
104       end
105   
106   
107   
108       /* check to see if we are using HA specific SP for a HA enabled server */
109       exec @retstat = sp_ha_check_certified 'sp_drop_resource_limit', @HA_CERTIFIED
110       if (@retstat != 0)
111           return (1)
112   
113       if @@trancount > 0
114       begin
115           /*
116           ** 17260, "Can't run %1! from within a transaction." 
117           */
118           raiserror 17260, "sp_drop_resource_limit"
119           return (1)
120       end
121   
122       if ((@name is null) and (@appname is null))
123       begin
124           /*
125           ** 18202, "At least one of the login or application name must be non-NULL."
126           */
127           raiserror 18202
128           return (1)
129       end
130   
131       /* If a user name was specified, make sure it's legal */
132       if ((@name is not null) and not exists
133                   (select * from master.dbo.syslogins where name = @name and
134                       ((status & 512) != 512))) /* not LOGIN PROFILE */
135       begin
136           /*
137           ** 17231, "No login with the specified name exists."
138           */
139           raiserror 17231
140           return (1)
141       end
142   
143       /* If a range name was specified, make sure it's legal */
144   
145       select @rangename = rtrim(@rangename)
146   
147       if ((@rangename is not null) and not exists
148                   (select * from master.dbo.systimeranges where name = @rangename))
149       begin
150           /*
151           ** 18199, "Unknown time range name '%1!'."
152           */
153           raiserror 18199, @rangename
154           return (1)
155       end
156   
157       /* If a limit type was specified, make sure it's legal */
158       if ((@limittype is not null) and not exists
159                   (select * from master.dbo.spt_limit_types where name = @limittype))
160       begin
161           /*
162           ** 18204, "Unknown limit type '%1!'."
163           */
164           raiserror 18204, @limittype
165           return (1)
166       end
167   
168       /* If an enforcement time was specified, make sure it's legal */
169       if ((@enforced is not null) and (@limittype is not null) and
170               ((@enforced & (select enforced from master.dbo.spt_limit_types
171                       where name = @limittype)) != @enforced))
172       begin
173           /*
174           ** 18208, "Illegal enforcement-time value %1! for this limit type."
175           */
176           raiserror 18208, @enforced
177           return (1)
178       end
179   
180       /* If an action was specified, make sure it's legal */
181       if ((@action is not null) and ((@action < 1) or (@action > 4)))
182       begin
183           /*
184           ** 18207, "Illegal action %1!."
185           */
186           raiserror 18207, @action
187           return (1)
188       end
189   
190       /* If a scope was specified, make sure it's legal */
191       if ((@scope is not null) and (@limittype is not null) and
192               ((@scope & (select scope from master.dbo.spt_limit_types
193                       where name = @limittype)) != @scope))
194       begin
195           /*
196           ** 18209, "Illegal scope value %1! for this limit type."
197           */
198           raiserror 18209, @scope
199           return (1)
200       end
201   
202       select @limitid = id from master.dbo.spt_limit_types where name = @limittype
203   
204       select @rangeid = id from master.dbo.systimeranges where name = @rangename
205   
206       /* Print a message (but still return successfully) if
207       ** there was no such limit to drop.
208       */
209       if ((select count(*) from master.dbo.sysresourcelimits where
210                       ((((name = @name) and
211                                   (appname = @appname)) or
212                               ((name = @name) and
213                                   (@appname is null)) or
214                               ((@name is null) and
215                                   (appname = @appname))) and
216                           ((@rangename is null) or (rangeid = @rangeid)) and
217                           ((@limittype is null) or (limitid = @limitid)) and
218                           ((@enforced is null) or ((enforced & @enforced) != 0)) and
219                           ((@action is null) or (action = @action)) and
220                           ((@scope is null) or ((scope & @scope) != 0)))
221                   ) = 0)
222       begin
223           /*
224           ** 18220, "No such limit found in sysresourcelimits."
225           */
226           exec sp_getmessage 18220, @msg output
227           print @msg
228           return (0)
229       end
230   
231   
232   
233       /* Delete! */
234       delete from master.dbo.sysresourcelimits where
235           ((((name = @name) and
236                       (appname = @appname)) or
237                   ((name = @name) and
238                       (@appname is null)) or
239                   ((@name is null) and
240                       (appname = @appname))) and
241               ((@rangename is null) or (rangeid = @rangeid)) and
242               ((@limittype is null) or (limitid = @limitid)) and
243               ((@enforced is null) or ((enforced & @enforced) != 0)) and
244               ((@action is null) or (action = @action)) and
245               ((@scope is null) or ((scope & @scope) != 0)))
246   
247   
248   
249       dbcc recachelimits
250   
251       /*
252       ** 18215, "Resource limit dropped."
253       */
254       exec sp_getmessage 18215, @msg output
255       print @msg
256   
257       return (0)
258   
259   clean_all:
260   
261       return (1)
262   


exec sp_procxmode 'sp_drop_resource_limit', 'AnyMode'
go

Grant Execute on sp_drop_resource_limit to public
go
DEFECTS
 MURC 6 Unreachable Code 259
 MURC 6 Unreachable Code 261
 QCSC 4 Costly 'select count()', use 'exists()' 209
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 219
 QTYP 4 Comparison type mismatch tinyint = int 219
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 244
 QTYP 4 Comparison type mismatch tinyint = int 244
 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 202
 MAW1 3 Warning message on %name% master..systimeranges.id: Warning message on systimeranges 204
 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_drop_resource_limit  
 MNER 3 No Error Check should check return value of exec 226
 MNER 3 No Error Check should check @@error after delete 234
 MNER 3 No Error Check should check return value of exec 254
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 261
 QAFM 3 Var Assignment from potentially many rows 202
 QAFM 3 Var Assignment from potentially many rows 204
 QPNC 3 No column in condition 213
 QPNC 3 No column in condition 214
 QPNC 3 No column in condition 216
 QPNC 3 No column in condition 217
 QPNC 3 No column in condition 218
 QPNC 3 No column in condition 219
 QPNC 3 No column in condition 220
 QPNC 3 No column in condition 238
 QPNC 3 No column in condition 239
 QPNC 3 No column in condition 241
 QPNC 3 No column in condition 242
 QPNC 3 No column in condition 243
 QPNC 3 No column in condition 244
 QPNC 3 No column in condition 245
 QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 216
 QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 217
 QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 241
 QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S 242
 VNRD 3 Variable is not read @dummy 97
 MSUB 2 Subquery Marker 133
 MSUB 2 Subquery Marker 148
 MSUB 2 Subquery Marker 159
 MSUB 2 Subquery Marker 170
 MSUB 2 Subquery Marker 192
 MSUB 2 Subquery Marker 209
 MTR1 2 Metrics: Comments Ratio Comments: 49% 65
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 30 = 40dec - 12exi + 2 65
 MTR3 2 Metrics: Query Complexity Complexity: 131 65

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