DatabaseProcApplicationCreatedLinks
sybsystemprocssp_displaylevel  14 déc. 14Defects Propagation Dependencies

1     
2     /* Stored procedure for assigning config display level for a user login.
3     **
4     */
5     
6     /*
7     ** Generated by spgenmsgs.pl on Thu Feb  2 00:39:16 2006 
8     */
9     /*
10    ** raiserror Messages for displaylevel [Total 5]
11    **
12    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
13    ** 18150, "The login '%1!' does not exist."
14    ** 18152, "Invalid display level. The valid values are 'basic', 'intermediate' or 'comprehensive'."
15    ** 18388, "You must be in the master database in order to run '%1!'."
16    ** 18409, "The built-in function '%1!' failed. Please see the other messages printed along with this message."
17    */
18    /*
19    ** sp_getmessage Messages for displaylevel [Total 2]
20    **
21    ** 18151, "The current display level for login '%1!' is '%2!'."
22    ** 18153, "The display level for login '%1!' has been changed to '%2!'."
23    */
24    /*
25    ** End spgenmsgs.pl output.
26    */
27    
28    create or replace procedure sp_displaylevel
29        @loginame varchar(30) = NULL, /* login name */
30        @level varchar(30) = NULL /* config display level */
31    as
32    
33        declare @userid int
34        declare @username varchar(30)
35        declare @cur_level_int int
36        declare @cur_level_char varchar(20)
37        declare @msg varchar(1024)
38    
39        declare @log_for_rep int
40        declare @db_rep_level_all int
41        declare @db_rep_level_none int
42        declare @db_rep_level_l1 int
43        declare @lt_rep_get_failed int
44    
45        declare @nullarg char(1)
46        declare @dummy int
47        declare @status int
48        declare @gp_enabled int
49    
50    
51        /*
52        ** Initialize some constants
53        */
54        select @db_rep_level_all = - 1,
55            @db_rep_level_none = 0,
56            @db_rep_level_l1 = 1,
57            @lt_rep_get_failed = - 2
58    
59        select @userid = NULL
60        select @cur_level_int = NULL
61    
62        /*
63        ** Get the replication status of the 'master' database
64        */
65        select @log_for_rep = getdbrepstat(1)
66        if (@log_for_rep = @lt_rep_get_failed)
67        begin
68            raiserror 18409, "getdbrepstat"
69            return (1)
70        end
71    
72        /*
73        ** Convert the replication status to a boolean
74        */
75        if (@log_for_rep != @db_rep_level_none)
76            select @log_for_rep = 1
77        else
78            select @log_for_rep = 0
79    
80        /*
81        ** If we are logging this system procedure for replication, we must be in
82        ** the 'master' database to avoid creating a multi-database transaction
83        ** which could make recovery of the 'master' database impossible.
84        */
85        if (@log_for_rep = 1) and (db_name() != "master")
86        begin
87            raiserror 18388, "sp_displaylevel"
88            return (1)
89        end
90    
91        /*
92        ** set the user login name
93        */
94        if (@loginame is NULL)
95            select @loginame = suser_name()
96    
97        /*
98        ** set the user id
99        */
100       select @userid = suser_id(@loginame)
101   
102       /*
103       ** check for invalid @loginame
104       */
105       if (@userid is NULL)
106       begin
107           /*  18150, "The login '%s' does not exist." */
108           raiserror 18150, @loginame
109           return (1)
110       end
111   
112       /*
113       ** check if user has permission to change the display level
114       ** only the user himself or user has sa role can has the permission
115       */
116       select @username = suser_name()
117       if (@loginame != @username)
118       begin
119           /* 
120           ** If granular permissions is not enabled then sa_role is required.
121           ** If granular permissions is enabled then the permission 
122           ** 'manage server configuration' is required.  proc_role and 
123           ** proc_auditperm will also do auditing if required. Both will also 
124           ** print error message if required.
125           */
126   
127           select @nullarg = NULL
128           execute @status = sp_aux_checkroleperm "sa_role",
129               "manage server configuration", @nullarg, @gp_enabled output
130   
131           /* For Auditing */
132           if (@gp_enabled = 0)
133           begin
134               if (proc_role("sa_role") = 0)
135                   return (1)
136           end
137           else
138           begin
139               select @dummy = proc_auditperm("manage server configuration",
140                       @status)
141           end
142   
143           if (@status != 0)
144               return (1)
145       end
146   
147       /*
148       ** if @level are NULL, just display the user config level
149       */
150       if (@level is NULL)
151       begin
152           /*
153           ** retrieve the user config level from sysattributes
154           */
155           select @cur_level_int = int_value from master.dbo.sysattributes where
156               class = 4 and
157               attribute = 0 and
158               object_type = 'L' and
159               object = @userid
160   
161           if (@cur_level_int = 10)
162               select @cur_level_char = "comprehensive"
163           else if (@cur_level_int = 5)
164               select @cur_level_char = "intermediate"
165           else if (@cur_level_int = 1)
166               select @cur_level_char = "basic"
167           else
168               select @cur_level_char = "comprehensive"
169   
170           exec sp_getmessage 18151, @msg output
171           print @msg, @loginame, @cur_level_char
172   
173           return (0)
174       end
175   
176       /*
177       ** check if the @level is valid
178       ** the current valid value is 'basic', 'intermediate', and 'comprehensive'.
179       */
180       if (@level = "basic")
181           select @cur_level_int = 1
182       else if (@level = "intermediate")
183           select @cur_level_int = 5
184       else if (@level = "comprehensive")
185           select @cur_level_int = 10
186       else
187       begin
188           /*  18152, "Invalid display level. 
189           **	The valid values are '1', '5' or '10'."
190           */
191           raiserror 18152
192           return (1)
193       end
194   
195       if (@log_for_rep = 1)
196           begin tran rs_logexec
197   
198       /*
199       ** Figure out if we need to insert or update from sysattributes. 
200       */
201       if exists (select * from master.dbo.sysattributes where
202                   class = 4 and
203                   attribute = 0 and
204                   object_type = 'L' and
205                   object = @userid)
206       begin
207           update master.dbo.sysattributes
208           set int_value = @cur_level_int
209           where class = 4 and
210               attribute = 0 and
211               object_type = 'L' and
212               object = @userid
213       end
214       else
215       begin
216           insert into master.dbo.sysattributes
217           (class, attribute, object_type, object, int_value)
218           VALUES
219           (4,
220               0,
221               'L',
222               @userid,
223               @cur_level_int)
224       end
225   
226       if (@log_for_rep = 1)
227       begin
228           /*
229           ** If the 'master' database is marked for replication, the T-SQL
230           ** built-in 'logexec()' will log for replication the execution
231           ** instance of this system procedure.  Otherwise, the T-SQL
232           ** built-in 'logexec()' is a no-op.
233           */
234           if (logexec(1) != 1)
235           begin
236               raiserror 17756, "sp_displaylevel", "master"
237   
238               if (@log_for_rep = 1)
239                   rollback tran rs_logexec
240               return (1)
241           end
242       end
243   
244       if (@log_for_rep = 1)
245           commit tran rs_logexec
246   
247       exec sp_getmessage 18153, @msg output
248       print @msg, @loginame, @level
249   
250       return (0)
251   


exec sp_procxmode 'sp_displaylevel', 'AnyMode'
go

Grant Execute on sp_displaylevel to public
go
DEFECTS
 MRIT 5 Return in Transaction trancount is 1 240
 MCTR 4 Conditional Begin Tran or Commit Tran 196
 MCTR 4 Conditional Begin Tran or Commit Tran 245
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 202
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 203
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 209
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 210
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_displaylevel  
 MNER 3 No Error Check should check return value of exec 170
 MNER 3 No Error Check should check @@error after update 207
 MNER 3 No Error Check should check @@error after insert 216
 MNER 3 No Error Check should check return value of exec 247
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 250
 QAFM 3 Var Assignment from potentially many rows 155
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 217
 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}
156
 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}
202
 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}
209
 VNRD 3 Variable is not read @db_rep_level_all 54
 VNRD 3 Variable is not read @db_rep_level_l1 56
 VNRD 3 Variable is not read @dummy 139
 MSUB 2 Subquery Marker 201
 MTR1 2 Metrics: Comments Ratio Comments: 42% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 30dec - 9exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 119 28

DATA PROPAGATION detailed
ColumnWritten To
@loginamesysattributes.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_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

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