DatabaseProcApplicationCreatedLinks
sybsystemprocssp_help_resource_limit  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_help_resource_limit"
6     **
7     ** 17231, "No login with the specified name exists."
8     ** 18207, "Illegal action %1!."
9     ** 18216, "Users other than the System Administrator (SA) may only view limits applicable to themselves."
10    ** 18217, "Illegal limit time argument '%1!'."
11    ** 18218, "Unknown limitday value '%1!'."
12    ** 
13    */
14    
15    create or replace procedure sp_help_resource_limit
16        @name varchar(30) = NULL, /* login to which limits apply */
17        @appname varchar(30) = NULL, /* application to which limits apply */
18        @limittime varchar(30) = NULL, /* time during which limits apply */
19        @limitday varchar(30) = NULL, /* day during which limits apply */
20        @scope int = NULL, /* scope of limits */
21        @action int = NULL, /* what to do if limits are exceeded */
22        @verbose int = 0 /* 1 means describe, 0 means default */
23    as
24    
25        declare @rangeid smallint
26        declare @limittime_dt datetime
27        declare @limitdaynum int
28        declare @procval int
29        declare @has_roleperm int /* sa_role or manage resource limit */
30        declare @nullarg char(1)
31        declare @dummy int
32        declare @status int
33        declare @gp_enabled int
34    
35    
36        /* 
37        ** If granular permissions is not enabled then sa_role is required.
38        ** If granular permissions is enabled then permission 'manage resource limit'
39        ** is required.  proc_role and proc_auditperm will also do auditing
40        ** if required. Both will also print error message if required.
41        */
42    
43        select @nullarg = NULL
44        execute @status = sp_aux_checkroleperm "sa_role", "manage resource limit",
45            @nullarg, @gp_enabled output
46    
47        /*
48        ** Check if the user executing the procedure
49        ** has sa_role. We use show_role() because
50        ** proc_role() will raise an error message
51        ** if the role is not enabled for this user.
52        */
53        if (@status = 0)
54            select @has_roleperm = 1
55        else
56            select @has_roleperm = 0
57    
58        select @procval = 0
59    
60        /* Is login valid? */
61        if ((@name is not null) and not exists
62                    (select * from master.dbo.syslogins where name = @name and
63                        ((status & 512) != 512))) /* not LOGIN PROFILE */
64        begin
65            /*
66            ** 17231, "No login with the specified name exists."
67            */
68            raiserror 17231
69            return (1)
70        end
71    
72        if ((@gp_enabled = 0) and (@has_roleperm = 0) and (@name is not null) and (@name != suser_name()))
73        begin
74            /*
75            ** 18216, "Users other than the System Administrator (SA) may only view limits applicable to themselves."
76            */
77            raiserror 18216
78            return (1)
79        end
80        else if ((@gp_enabled = 1) and (@has_roleperm = 0) and (@name is not null) and
81                (@name != suser_name()))
82        begin
83            select @dummy = proc_auditperm("manage resource limit", @status)
84            return (1)
85        end
86    
87        if (@has_roleperm > 0)
88        begin
89            if (@gp_enabled = 0)
90            begin
91                select @dummy = proc_role("sa_role")
92            end
93            else
94            begin
95                select @dummy = proc_auditperm("manage resource limit", @status)
96            end
97        end
98    
99        /* Has a legal action been specified? */
100       if (@action is not null) and ((@action < 1) or (@action > 5))
101       begin
102           /*
103           ** 18207, "Illegal action %1!."
104           */
105           raiserror 18207, @action
106           return (1)
107       end
108   
109       /* Convert the limit time to datetime so we can do arithmetic */
110       if (@limittime is not null)
111       begin
112           select @limittime_dt = convert(datetime, @limittime)
113           if (@limittime_dt is null)
114           begin
115               /*
116               ** 18217, "Illegal limit time argument '%1!'."
117               */
118               raiserror 18217, @limittime
119               return (1)
120           end
121       end
122   
123       /* Convert the starting time to datetime so we can do arithmetic */
124       if (@limitday is not null)
125       begin
126           select @limitdaynum = daytonum(@limitday)
127           if (@limitdaynum = - 1)
128           begin
129               /*
130               ** 18218, "Unknown limitday value '%1!'."
131               */
132               raiserror 18218, @limitday
133               return (1)
134           end
135       end
136   
137       if (@verbose = 0)
138       begin
139           /* Find the limits that apply */
140           select l.name, l.appname, rangename = r.name, l.rangeid, l.limitid, l.limitvalue, l.enforced, l.action, l.scope
141           into #sphelpreslimit1rs
142           from master.dbo.sysresourcelimits l, master.dbo.systimeranges r
143           where (l.rangeid = r.id)
144               and
145               (
146                   (l.name = @name) or
147                   (l.name is null) or
148                   ((@has_roleperm = 1) and (@name is null)) or
149                   ((suser_name() = @name) and (l.name is null))
150               )
151               and
152               ((@appname is null) or (l.appname = @appname))
153               and
154               ((@scope is null) or (l.scope = @scope))
155               and
156               ((@action is null) or (l.action = @action))
157               and
158               (
159                   (
160                       (r.startday <= r.endday)
161                       and
162                       ((@limitday is null) or
163                           ((@limitdaynum >= r.startday) and
164                               (@limitdaynum <= r.endday)))
165                       and
166                       ((@limittime is null) or
167                           ((convert(datetime, r.starttime) <= @limittime_dt) and
168                               ((convert(datetime, r.endtime) > @limittime_dt) or
169                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
170                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
171                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
172                   )
173                   or
174                   (
175                       (r.startday >= r.endday)
176                       and
177                       ((@limitday is null) or
178                           ((@limitdaynum >= r.startday) or
179                               (@limitdaynum <= r.endday)))
180                       and
181                       ((@limittime is null) or
182                           ((convert(datetime, r.starttime) <= @limittime_dt) and
183                               ((convert(datetime, r.endtime) > @limittime_dt) or
184                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
185                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
186                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
187                   )
188               )
189           exec sp_autoformat @fulltabname = #sphelpreslimit1rs
190           drop table #sphelpreslimit1rs
191       end
192       else
193       begin
194           /* Find the limits that apply */
195           select l.name, l.appname, rangename = r.name, l.rangeid,
196               case l.limitid
197                   when 1 then convert(char(15),
198                   "io_cost" + "(" + ltrim(str(l.limitid)) + ")")
199                   when 2 then convert(char(15),
200                   "elapsed_time" + "(" + ltrim(str(l.limitid)) + ")")
201                   when 3 then convert(char(15),
202                   "row_count" + "(" + ltrim(str(l.limitid)) + ")")
203                   when 4 then convert(char(15),
204                   "tempdb_space" + "(" + ltrim(str(l.limitid)) + ")")
205                   when 5 then convert(char(15),
206                   "cpu_time" + "(" + ltrim(str(l.limitid)) + ")")
207               end AS "limitType",
208               l.limitvalue,
209               case l.enforced
210                   when 1 then convert(char(16), "Compilation Time")
211                   when 2 then convert(char(16), "Execution Time")
212                   when 3 then convert(char(16), "Any Time")
213               end AS "enforced",
214               case l.action
215                   when 0 then convert(char(17), "No Action")
216                   when 1 then convert(char(17), "Warn")
217                   when 2 then convert(char(17), "Abort Batch")
218                   when 3 then convert(char(17), "Abort Transaction")
219                   when 4 then convert(char(17), "Kill Session")
220                   when 5 then convert(char(17), "Record Action")
221               end AS "action",
222               case l.scope
223                   when 1 then convert(char(11), "Query")
224                   when 2 then convert(char(11), "Batch")
225                   when 4 then convert(char(11), "Transaction")
226                   when 6 then convert(char(11), "Batch/Tran")
227               end AS "scope"
228           into #sphelpreslimit1rs_v
229           from master.dbo.sysresourcelimits l, master.dbo.systimeranges r
230           where (l.rangeid = r.id)
231               and
232               (
233                   (l.name = @name) or
234                   (l.name is null) or
235                   ((@has_roleperm = 1) and (@name is null)) or
236                   ((suser_name() = @name) and (l.name is null))
237               )
238               and
239               ((@appname is null) or (l.appname = @appname))
240               and
241               ((@scope is null) or (l.scope = @scope))
242               and
243               ((@action is null) or (l.action = @action))
244               and
245               (
246                   (
247                       (r.startday <= r.endday)
248                       and
249                       ((@limitday is null) or
250                           ((@limitdaynum >= r.startday) and
251                               (@limitdaynum <= r.endday)))
252                       and
253                       ((@limittime is null) or
254                           ((convert(datetime, r.starttime) <= @limittime_dt) and
255                               ((convert(datetime, r.endtime) > @limittime_dt) or
256                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
257                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
258                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
259                   )
260                   or
261                   (
262                       (r.startday >= r.endday)
263                       and
264                       ((@limitday is null) or
265                           ((@limitdaynum >= r.startday) or
266                               (@limitdaynum <= r.endday)))
267                       and
268                       ((@limittime is null) or
269                           ((convert(datetime, r.starttime) <= @limittime_dt) and
270                               ((convert(datetime, r.endtime) > @limittime_dt) or
271                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
272                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
273                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
274                   )
275               )
276           exec sp_autoformat @fulltabname = #sphelpreslimit1rs_v
277           drop table #sphelpreslimit1rs_v
278   
279       end
280   
281       return (0)
282   
283   


exec sp_procxmode 'sp_help_resource_limit', 'AnyMode'
go

Grant Execute on sp_help_resource_limit to public
go
DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 189
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 276
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 154
 QTYP 4 Comparison type mismatch tinyint = int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 156
 QTYP 4 Comparison type mismatch tinyint = int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 163
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 179
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 241
 QTYP 4 Comparison type mismatch tinyint = int 241
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 243
 QTYP 4 Comparison type mismatch tinyint = int 243
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 250
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 251
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 265
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 266
 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..systimeranges.id: Warning message on systimeranges 143
 MAW1 3 Warning message on %name% master..systimeranges.id: Warning message on systimeranges 230
 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_help_resource_limit  
 MNER 3 No Error Check should check @@error after select into 140
 MNER 3 No Error Check should check return value of exec 189
 MNER 3 No Error Check should check @@error after select into 195
 MNER 3 No Error Check should check return value of exec 276
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 281
 QCTC 3 Conditional Table Creation 140
 QCTC 3 Conditional Table Creation 195
 QNAJ 3 Not using ANSI Inner Join 142
 QNAJ 3 Not using ANSI Inner Join 229
 QPNC 3 No column in condition 148
 QPNC 3 No column in condition 149
 QPNC 3 No column in condition 152
 QPNC 3 No column in condition 154
 QPNC 3 No column in condition 156
 QPNC 3 No column in condition 162
 QPNC 3 No column in condition 166
 QPNC 3 No column in condition 177
 QPNC 3 No column in condition 181
 QPNC 3 No column in condition 235
 QPNC 3 No column in condition 236
 QPNC 3 No column in condition 239
 QPNC 3 No column in condition 241
 QPNC 3 No column in condition 243
 QPNC 3 No column in condition 249
 QPNC 3 No column in condition 253
 QPNC 3 No column in condition 264
 QPNC 3 No column in condition 268
 VNRD 3 Variable is not read @procval 58
 VNRD 3 Variable is not read @dummy 95
 VUNU 3 Variable is not used @rangeid 25
 MSUB 2 Subquery Marker 62
 MTR1 2 Metrics: Comments Ratio Comments: 21% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 82 = 87dec - 7exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 146 15
 PRED_QUERY_COLLECTION 2 {r=master..sysresourcelimits, t=master..systimeranges} 0 140
 PRED_QUERY_COLLECTION 2 {r=master..sysresourcelimits, t=master..systimeranges} 0 195

DEPENDENCIES
PROCS AND TABLES USED
reads table master..systimeranges (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table master..sysresourcelimits (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
writes table tempdb..#sphelpreslimit1rs (1) 
writes table tempdb..#sphelpreslimit1rs_v (1) 
reads table master..syslogins (1)